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

Jim Nasby <jim(at)nasby(dot)net> writes:
> On Oct 31, 2013, at 11:04 AM, Joe Love <joe(at)primoweb(dot)com> wrote:
>> In postgres 9.2 I have a function that is relatively expensive. When I write a query such as:
>>
>> select expensive_function(o.id),o.* from offeirng o where valid='Y' order by name limit 1;

> Does anyone know what the SQL standard says about this, if anything?

The computational model is that you evaluate the SELECT list before
sorting; this must be so since you can write

select somefunc(x) as y from tab order by y;

In the general case, therefore, it's impossible to avoid evaluating the
function at all rows. I'm not sure what the spec says about whether it's
okay to skip evaluation of functions that would be evaluated in a naive
implementation of the computational model, so it's possible that what
the OP is asking for is directly contrary to spec. But more likely they
permit implementations to skip "unnecessary" calls, if indeed they address
this at all.

As far as PG goes, I think the "excess" calls would only occur if the plan
includes an explicit sort step, since the select list would be evaluated
before the sort step. If you've got an index on "name" (or maybe you'd
need (valid, name) if there aren't many rows with valid = 'Y') I'd expect
it to pick out the minimal "name" row with the index, avoiding any sort,
and then the function would only be evaluated on the single fetched row.
But these are implementation details not anything you're going to find
support for in the spec.

regards, tom lane