Re: Quota query with decent performance?

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