David G Johnston <david.g.johnston@gmail.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.
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.
regards, tom lane
