Can improve 'limit 1' ? with slow function

From: "songtebo"<songtebo(at)126(dot)com>
To: "pgsql-performance"<pgsql-performance(at)postgresql(dot)org>
Subject: Can improve 'limit 1' ? with slow function
Date: 2014-06-27 07:30:52
Message-ID: 44934c03.2b413d.146dc3c04c6.Coremail.songtebo@126.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

The simplified scene:
select slowfunction() from a order by b limit 1
is slow than
select slowfunction() from ( select * from a order by b limit 1)
if there are many records in table 'a'

The real scene:

function ST_Distance_Sphere is slow than ST_Distance, the query:

SELECT ST_Distance_Sphere(s, ST_GeomFromText('POINT(1 1)')) from road order by ST_Distance(s, ST_GeomFromText('POINT(1 1)')) limit 1

is slow than:

select ST_Distance_Sphere(s, ST_GeomFromText('POINT(1 1)')) from (SELECT s from road order by ST_Distance(s, ST_GeomFromText('POINT(1 1)')) limit 1) as a

There are about 7000 records in 'road'.

Browse pgsql-performance by date

  From Date Subject
Next Message Andres Freund 2014-06-27 09:19:51 Re: 60 core performance with 9.3
Previous Message Mark Kirkwood 2014-06-27 02:28:20 Re: 60 core performance with 9.3