On Tue, Jul 1, 2014 at 3:06 PM, David G Johnston <david.g.johns...@gmail.com> wrote: > Merlin Moncure-2 wrote >> On Tue, Jul 1, 2014 at 2:16 PM, Martijn van Oosterhout >> < > >> kleptog@ > >> > 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).
Sure, although I did not claim that..the select documentation *does* cover this behavior but I find the syntax driven doc pages to be fairly arcane and unhelpful -- they don't say (for the most part) "avoid this" or "do that". I pointed out this particular section because it proved an example that matched the OP's problem case. merlin -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers