On Tue, Jul 1, 2014 at 3:06 PM, David G Johnston
<david.g.johnston@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
