Thread: Feature request: optimizer improvement

Jim Nasby <jim@nasby.net> writes:
> On Oct 31, 2013, at 11:04 AM, Joe Love <joe@primoweb.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

On Sat, Nov 2, 2013 at 2:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jim Nasby <jim@nasby.net> writes:
>> On Oct 31, 2013, at 11:04 AM, Joe Love <joe@primoweb.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

Doesnt that make the index mandatory here?
If I understand correctly, if an index is present, the sort will be
avoided altogether. IMHO, thats avoiding the problem. The question
here is that whether we can add planner heuristics for understanding
this case and executing the LIMIT part first (before executing the
funtion).

I understand the reasons for executing SELECT before the sort. But,
couldnt we get the planner to see the LIMIT part and push the sort
node above the select node for this specific case?

So, seeing the LIMIT, the dataset is first sorted, then LIMITed, then
the function applied.

Is this process possible?

Regards,

Atri
-- 
Regards,

Atri
l'apprenant

Atri Sharma <atri.jiit@gmail.com> writes:
> I understand the reasons for executing SELECT before the sort. But,
> couldnt we get the planner to see the LIMIT part and push the sort
> node above the select node for this specific case?

[ Shrug... ]  I don't see the point.  If the OP actually cares about the
speed of this query, he's going to want to avoid the sort step too,
which is what makes the index a good idea.

More generally, this is not a transformation we could apply
unconditionally --- at the very least it'd need to be avoided when
volatile functions are involved, and I don't think it's invariably
a win from a cost standpoint even if there aren't semantic blockers.
But right now the planner has no real ability to reason about placement
of SELECT-list evaluation: it's done in a fixed spot in any particular
plan structure.  I don't think it's practical to add such considerations
to the rat's nest that is grouping_planner and friends.  I have
ambitions to replace all that with a Path-generation-and-comparison
approach, and the Paths used for this would need to carry some
indication of which expressions would be evaluated where.  So maybe
once that's done we could think about whether this is worth doing.
I remain dubious though.
        regards, tom lane

Joe Love <joe@primoweb.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.
        regards, tom lane

On Tue, Nov 5, 2013 at 11:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joe Love <joe@primoweb.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