Re: Nested query performance issue

2009/4/9 Glenn Maynard <glennfmaynard(at)gmail(dot)com>

> (This is related to an earlier post on -sql.)
>
> I'm querying for the N high scores for each game, with two tables:
> scores and games.
>
> CREATE TABLE game (id SERIAL NOT NULL PRIMARY KEY);
> CREATE TABLE score (id SERIAL NOT NULL PRIMARY KEY, score REAL,
> game_id INTEGER REFERENCES game (id));
> -- test data: 1000 games, 100000 scores
> INSERT INTO game (id) select generate_series(1,1000);
> INSERT INTO score (game_id, score) select game.id, random() from game,
> generate_series(1,100);
> CREATE INDEX score_idx1 ON score (game_id, score desc);
> ANALYZE;
>

How about

select s1.*
from score s1 join score s2 on s1.game_id=s2.game_id and s2.score >=
s1.score
group by s1.*
having count(s2.*) <= N

Note: you can have problems if you have same scores - you will loose last
group that overlap N

In any case, you don't need to join game since all you need is game_id you
already have in score.

P.S. EXPLAIN ANALYZE could help

Best regards, Vitalii Tymchyshyn