Jim Nasby <j...@nasby.net> writes: > On Oct 31, 2013, at 11:04 AM, Joe Love <j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers