On Tue, Jul 1, 2014 at 2:16 PM, Martijn van Oosterhout
<klep...@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:
In short, if you want very precise control of function evaluation use
a subquery, or, if you're really paranoid, a CTE.


Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to