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

From: David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Can simplify 'limit 1' with slow function?
Date: 2014-07-01 20:06:12
Message-ID: 1404245172271-5810061.post@n5.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

> kleptog@

> &gt; 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.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Abhijit Menon-Sen 2014-07-01 20:13:24 Re: pg_xlogdump --stats
Previous Message Robert Haas 2014-07-01 19:57:25 Re: /proc/self/oom_adj is deprecated in newer Linux kernels