Re: Can simplify 'limit 1' with slow function?

On Wed, Jul 02, 2014 at 04:17:13PM -0400, Tom Lane wrote:
> David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Martijn van Oosterhout wrote
> >> I'm probably dense, but I'm not sure I understand. Or it is that the
> >> slowfunction() is called prior to the sort? That seems insane.
>
> > The basic reality is that limit applies to the final set of rows that could
> > be output.
>
> It's not so much the limit as that the sort has to happen before the
> limit, and yes, evaluation of the targetlist happens before the sort.

I guess I assumed the column c was indexable, and it that case I
beleive the slowfunction() would indeed only be called once.

> This is fundamental to the SQL conceptual model; remember that SQL92 had
> "SELECT slowfunction(), ... ORDER BY 1", which certainly requires the
> function to be evaluated before the sort happens. And there's nothing in
> the conceptual model suggesting that different targetlist entries should
> be evaluated at different times, so just ordering by something other than
> the slowfunction() entry doesn't get you out of that.
>
> I'm not sure how much of this there is chapter and verse for in the
> SQL standard, but ISTM the stage sequencing we lay out in our SELECT
> reference page is pretty much forced by the standard.

In the conceptual model the limit must happen after the select. But as
an optimisation moving the evaluation above the limit node (when
possible) should always be a win.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer