Re: Handle LIMIT/OFFSET before select clause (was: Feature request: optimizer improvement)

On Tue, Nov 5, 2013 at 11:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Joe Love <joe(at)primoweb(dot)com> writes:
>> I'm wondering what type of index would work for this as it is a volatile
>> function. Not knowing how PGs optimizer runs, I'm at a loss as to why this
>> wouldn't be possible or worth doing. It seems to me that all functions in
>> the "select" part of the statement could be calculated at the end of the
>> query after the results have been gathered, and even after the sorting had
>> been done as long as the column wasn't part of the order by (or perhaps
>> group by).
>
> The short answer is that doing so directly contradicts the computational
> model defined by the SQL standard, and will break applications that rely
> on the current behavior. Since there's already a clear way to write the
> query in a way that specifies evaluating the functions after the
> sort/limit steps (ie, put the order by/limit in a sub-select), IMHO that's
> what you should do, not lobby to make the optimizer reinterpret what you
> wrote.
>
>

+1.

I thought more about our earlier discussion on this, and I agree with
the point that making the planner push limit over select for this
specific case is not a good idea.

--
Regards,

Atri
l'apprenant