Merlin Moncure-2 wrote
> On Tue, Jul 1, 2014 at 2:16 PM, Martijn van Oosterhout
> <

> kleptog@

> > wrote:
>> On Sun, Jun 29, 2014 at 10:05:50PM +0800, gotoschool6g wrote:
>>> The simplified scene:
>>> select slowfunction(s) from a order by b limit 1;
>>> is slow than
>>> select slowfunction(s) from (select s from a order by b limit 1) as z;
>>> if there are many records in table 'a'.
>>> The real scene. Function  ST_Distance_Sphere is slow, the query:
>>> SELECT ST_Distance_Sphere(s, ST_GeomFromText('POINT(1 1)')) from road
>>> order by c limit 1;
>>> is slow than:
>>> select ST_Distance_Sphere(s, ST_GeomFromText('POINT(1 1)')) from (SELECT
>>> s from road order by c limit 1) as a;
>>> There are about 7000 records in 'road'.
>> I think to help here I think we need the EXPLAIN ANALYSE output for
>> both queries.
> Well, I think the problem is a well understood one: there is no
> guarantee that functions-in-select-list are called exactly once per
> output row.  This is documented -- for example see here:
> In short, if you want very precise control of function evaluation use
> a subquery, or, if you're really paranoid, a CTE.
> merlin

I would have to disagree on the "this is documented" comment - the linked
section on advisory locks does not constitute documentation of the fact that
limit can be applied after expressions in the select-list are evaluated.

In the select command documentation item 5 covers select-list evaluation
while item 9 covers limit thus implying what we are saying - though keep in
mind each select statement gets processed independently and possibly in a
correlated fashion (i.e. potentially multiple times).

David J.

View this message in context:
Sent from the PostgreSQL - hackers mailing list archive at

Sent via pgsql-hackers mailing list (
To make changes to your subscription:

Reply via email to