Re: Nested query performance issue

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