On Wed, Feb 22, 2012 at 20:53, <kou...@saparev.com> wrote: > -- shows a notice for 1, 2, 3 and 4 > SELECT x.id, f(x) FROM xxx as x LIMIT 2 OFFSET 2;
Currently the way to fix this is to use a subquery that acts as an optimization barrier in the presence of OFFSET: SELECT x.id, f(x) FROM (SELECT * FROM xxx as x LIMIT 2 OFFSET 2) as xxx; > The rows of a SELECT statement are being evaluated, even when not shown in > the final result, when using an OFFSET > 0. Although I know that LIMIT is > imposed just before flushing the result set to the client, this behaviour > seems quite confusing, especially when using DML statements in the field > list of the SELECT itself. Interesting, the model for evaluating queries is documented here: http://www.postgresql.org/docs/9.1/static/sql-select.html According to this model, evaluating SELECT clause fields for *all* found rows is done in step 5, whereas LIMIT/OFFSET are only applied later at step 9. So we're already bending the rules here (in general we don't do such optimizations around volatile functions). The worst thing is that it's inconsistent -- the LIMIT gets applied when computing the SELECT list, but OFFSET doesn't. In theory we could bend the model even more -- to push SELECT list fields below the "Limit" node if they aren't referenced by ORDER and there are no set operations. However, adapting the model to back to this behavior seems rather impossible -- ORDER BY must be strictly evaluated after SELECT list (it can refer to SELECT fields), and LIMIT must be evaluated after ORDER BY, otherwise it makes no sense. Or going the other way -- we could make it evaluate all rows if the SELECT list if it contains volatile functions, and then apply the LIMIT afterwards. That would go even more against "common sense", but at least it would be "correct" :) Regards, Marti -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
