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

On Tue, Jul 1, 2014 at 2:16 PM, Martijn van Oosterhout
<kleptog@svana.org> 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