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

On 02/23/2012 12:05 AM, Marti Raudsepp wrote:
> 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.

Indeed, that's probably the main issue - it is not behaving
symmetrically, i.e. fetching the first two rows has one effect (and
performance impact), while fetching the last two - completely different.

In my case, I am making something like an "ON SELECT" rule, triggering
some actions once the rows are read (and sent to the client) from a
SELECT statement. The thing is that "read" and "sent to the client"
appear to be two different things in that case. While I will certainly
use a subquery for it, as proposed by Marti (since real cursors are not
an option in my stateless web environment), I do believe that at least
the documentation should be more clear concerning cases like that (if
the behaviour stays that way).

Regards,
--
Kouber Saparev