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: 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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers