From: Josh Berkus <josh@agliodbs.com>
To: Troels Arvin <troels@arvin.dk>
To: pgsql-sql@postgresql.org
Subject: Re: Quota query with decent performance?
Date: Tue, 11 Nov 2003 15:41:34 -0800
Message-ID: <200311111541.34660.josh@agliodbs.com> (raw)
In-Reply-To: <pan.2003.11.11.22.58.08.758402@arvin.dk>
References: <pan.2003.11.11.22.58.08.758402@arvin.dk>
Troels,
Thank you for contacting us before publishing your results. Please ignore
any list-trolls who criticize your methodology; there are a few cranks on
every list. The important thing is your contacted us.
> In MSSQL and DB2 there are very efficient facilities for such queries, but
> I can't find any well-performing declarative methods for PostgreSQL. I
> have tried a couple of different strategies, and I currently get the best
> results from a correlated subquery like
>
> SELECT * FROM person AS px
> WHERE (
> SELECT COUNT(*)
> FROM person AS py
> WHERE py.age < px.age
> ) < 3;
>
> When my base table has 4000 rows, my query takes 27 seconds in PostgreSQL
> 7.2.3 (PIII 1000MHz) which is clearly unacceptable,
Well, first off 7.2.3 is a two-year old version. There have been performance
improvements specificially on these sorts of issues since then. Please use
at least 7.3.4, and we would prefer that you use 7.4RC2, which is available
now. 7.4, in particular, introduces significant improvements in "group by"
queries. If for some reason you have to compare against this old version,
please be fair to us and note somewhere that you were using an old version of
PostgreSQL.
Second, the query you post is one "SQL Standard" way, which is good for
portability but not for speed. Frankly, I'm not convinced that it's even the
best SQL standard way. On the other databases, you seem happy to use
non-SQL-standard syntax, so let me give you one such solution in PostgreSQL:
SELECT * FROM person
WHERE person.age >= (SELECT p2.age
from person p2
order by p2.age DESC LIMIT 1 OFFSET 2)
also try:
SELECT *
FROM person,
(SELECT p2.age
from person p2
order by p2.age DESC LIMIT 1 OFFSET 2) as prank
WHERE person.age >= prank.age
This should give you all of the rows whose ages are in the top 3 ranks much
faster.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
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-sql@postgresql.org
Cc: josh@agliodbs.com, troels@arvin.dk
Subject: Re: Quota query with decent performance?
In-Reply-To: <200311111541.34660.josh@agliodbs.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