On Tue, Apr 14, 2009 at 5:33 AM, Matthew Wakeling <matthew@flymine.org> 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