Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Troels Arvin <troels@arvin.dk>, pgsql-sql@postgresql.org
Subject: Re: Quota query with decent performance?
Date: Tue, 11 Nov 2003 15:41:34 -0800
User-Agent: KMail/1.4.3
References: <pan.2003.11.11.22.58.08.758402@arvin.dk>
In-Reply-To: <pan.2003.11.11.22.58.08.758402@arvin.dk>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200311111541.34660.josh@agliodbs.com>

Troels,

Thank you for contacting us before publishing your results.   Please ignore=
=20
any list-trolls who criticize your methodology; there are a few cranks on=
=20
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
>=20
> SELECT * FROM person AS px
> WHERE (
>   SELECT COUNT(*)
>   FROM person AS py
>   WHERE py.age < px.age
> ) < 3;
>=20
> When my base table has 4000 rows, my query takes 27 seconds in PostgreSQL
> 7.2.3 (PIII 1000MHz) which is clearly unacceptable,=20

Well, first off 7.2.3 is a two-year old version.   There have been performa=
nce=20
improvements specificially on these sorts of issues since then.  Please use=
=20
at least 7.3.4, and we would prefer that you use 7.4RC2, which is available=
=20
now.  7.4, in particular, introduces significant improvements in "group by"=
=20
queries. If for some reason you have to compare against this old version,=
=20
please be fair to us and note somewhere that you were using an old version =
of=20
PostgreSQL.

Second, the query you post is one "SQL Standard" way, which is good for=20
portability but not for speed.  Frankly, I'm not convinced that it's even t=
he=20
best SQL standard way.  On the other databases, you seem happy to use=20
non-SQL-standard syntax, so let me give you one such solution in PostgreSQL:

SELECT * FROM person
WHERE person.age >=3D (SELECT p2.age
	from person p2
	order by p2.age DESC LIMIT 1 OFFSET 2)

also try:

SELECT *=20
FROM person,=20
	(SELECT p2.age
	from person p2
	order by p2.age DESC LIMIT 1 OFFSET 2) as prank
WHERE person.age >=3D prank.age

This should give you all of the rows whose ages are in the top 3 ranks much=
=20
faster.

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco