On Tue, Jul 01, 2014 at 02:36:55PM -0500, Merlin Moncure wrote: > 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.
I'm probably dense, but I'm not sure I understand. Or it is that the slowfunction() is called prior to the sort? That seems insane. Have a nice day, -- Martijn van Oosterhout <klep...@svana.org> http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer
signature.asc
Description: Digital signature