On Thu, 9 Apr 2009, Glenn Maynard wrote:
On Thu, Apr 9, 2009 at 7:29 AM, Heikki Linnakangas wrote:
SELECT s.* FROM score s, game g
WHERE s.game_id = g.id AND
s.id IN (
SELECT s2.id FROM score s2 WHERE s2.game_id=g.id ORDER BY s2.score
DESC LIMIT 1
);
You don't really need the join with game here, simplifying this into:
SELECT s.* FROM score s
WHERE s.id IN (
SELECT s2.id FROM score s2 WHERE s2.game_id=s.game_id ORDER BY s2.score
DESC LIMIT 1
);
I don't think it makes it any faster, though.
It's about 10% faster for me. I'm surprised the planner can't figure
out that this join is redundant.
Because the join isn't redundant? You're making the assumption that for
every score.game_id there is exactly one game.id that matches. Of course,
you may have a unique constraint and foreign key/trigger that ensures
this.
Matthew
--
The third years are wandering about all worried at the moment because they
have to hand in their final projects. Please be sympathetic to them, say
things like "ha-ha-ha", but in a sympathetic tone of voice
-- Computer Science Lecturer
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
