From: songtebo <songtebo@126.com>
To: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Can improve 'limit 1' ? with slow function
Date: Fri, 27 Jun 2014 15:30:52 +0800
Message-ID: <44934c03.2b413d.146dc3c04c6.Coremail.songtebo@126.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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'.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: songtebo@126.com
Subject: Re: Can improve 'limit 1' ? with slow function
In-Reply-To: <44934c03.2b413d.146dc3c04c6.Coremail.songtebo@126.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox