Date: Fri, 27 Jun 2014 15:30:52 +0800
From: "songtebo"<songtebo@126.com>
To: "pgsql-performance"<pgsql-performance@postgresql.org>
Message-ID: <44934c03.2b413d.146dc3c04c6.Coremail.songtebo@126.com>
Subject: Can improve  'limit  1' ? with slow function
MIME-Version: 1.0
Content-Type: text/plain;
	charset="UTF-8"
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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'.  


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance