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

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:
> http://www.postgresql.org/docs/9.1/static/explicit-locking.html#ADVISORY-LOCKS.
> 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.

http://www.postgresql.org/docs/9.3/static/sql-select.html

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: http://postgresql.1045698.n5.nabble.com/Can-simplify-limit-1-with-slow-function-tp5809997p5810061.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.