Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-04 Thread Martijn van Oosterhout
Fascinating. On Fri, Jul 04, 2014 at 10:47:06AM +0800, gotoschool6g wrote: slow query(8531 ms): SELECT ST_Distance_Sphere(shape,ST_GeomFromText('POINT(116.41386186784513 40.12211338311868)')) FROM road order by id LIMIT 1; explain output: Limit (cost=4653.48..4653.48 rows=1 width=3612)

Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-03 Thread gotoschool6g
slow query(8531 ms): SELECT ST_Distance_Sphere(shape,ST_GeomFromText('POINT(116.41386186784513 40.12211338311868)')) FROM road order by id LIMIT 1; explain output: Limit (cost=4653.48..4653.48 rows=1 width=3612) - Sort (cost=4653.48..4683.06 rows=11832 width=3612) Sort Key: id

Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-02 Thread Martijn van Oosterhout
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

Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-02 Thread David G Johnston
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 lt; kleptog@ gt; wrote: On Sun, Jun 29, 2014 at 10:05:50PM +0800, gotoschool6g wrote: The simplified scene: select slowfunction(s) from a

Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-02 Thread Tom Lane
David G Johnston david.g.johns...@gmail.com writes: Martijn van Oosterhout wrote 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

Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-02 Thread Martijn van Oosterhout
On Wed, Jul 02, 2014 at 04:17:13PM -0400, Tom Lane wrote: David G Johnston david.g.johns...@gmail.com writes: Martijn van Oosterhout wrote 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

Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-02 Thread Tom Lane
Martijn van Oosterhout klep...@svana.org writes: On Wed, Jul 02, 2014 at 04:17:13PM -0400, Tom Lane wrote: It's not so much the limit as that the sort has to happen before the limit, and yes, evaluation of the targetlist happens before the sort. I guess I assumed the column c was indexable,

[HACKERS] Can simplify 'limit 1' with slow function?

2014-07-01 Thread gotoschool6g
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,

Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-01 Thread Martijn van Oosterhout
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

Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-01 Thread Merlin Moncure
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)

Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-01 Thread David G Johnston
Merlin Moncure-2 wrote On Tue, Jul 1, 2014 at 2:16 PM, Martijn van Oosterhout lt; kleptog@ gt; 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

Re: [HACKERS] Can simplify 'limit 1' with slow function?

2014-07-01 Thread Merlin Moncure
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 lt; kleptog@ gt; wrote: On Sun, Jun 29, 2014 at 10:05:50PM +0800, gotoschool6g wrote: The simplified scene: select