Re: Nested query performance issue

On Tue, Apr 14, 2009 at 5:33 AM, Matthew Wakeling <matthew(at)flymine(dot)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