Re: BUG #6483: Rows being evaluated, although being outside the LIMIT / OFFSET boundaries

On Wed, Feb 22, 2012 at 23:40, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Marti Raudsepp <marti(at)juffo(dot)org> writes:
>> 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.
>
> On what grounds do you say that?  LIMIT and OFFSET are practically the
> same thing internally, and are certainly applied in the same way.

The difference is that the SELECT fields for the first OFFSET rows are
*evaluated*, but aren't simply returned to the client. But beyond
LIMIT, query evaluation terminates entirely -- the rest of the SELECT
clause rows aren't evaluated.

AFAICT, the model in the documentation suggests that the SELECT fields
are evaluated for all matching rows in indeterminate order, before
ORDER BY is applied and before the result set is sliced by
OFFSET/LIMIT.

Regards,
Marti