On Tue, Apr 14, 2009 at 5:33 AM, Matthew Wakeling <matthew@xxxxxxxxxxx> wrote: >> 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. That's the definition of the tables I gave. CREATE TABLE game (id SERIAL NOT NULL PRIMARY KEY); -- pk implies unique CREATE TABLE score (id SERIAL NOT NULL PRIMARY KEY, score REAL, game_id INTEGER REFERENCES game (id)); (I don't think it makes any difference to whether this can be optimized, but adding NOT NULL back to game_id doesn't change it, either.) -- Glenn Maynard -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
