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
