Martijn van Oosterhout wrote
> 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
>> <
> 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.
>
> 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.
The basic reality is that limit applies to the final set of rows that could
be output. Since stuff like group by and distinct require knowledge of the
exact values of every output column all expressions must necessarily be
evaluated before limit.
If you want to pick just 10 rows and then process them you need a subquery.
David J.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Can-simplify-limit-1-with-slow-function-tp5809997p5810297.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.