Re: Can simplify 'limit 1' with slow function?

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@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   <kleptog@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