Re: Nested query performance issue

From: Matthew Wakeling
Subject: Re: Nested query performance issue
Date: ,
Msg-id: alpine.DEB.2.00.0904141029500.4053@aragorn.flymine.org
(view: Whole thread, Raw)
In response to: Re: Nested query performance issue  (Glenn Maynard)
Responses: Re: Nested query performance issue  (Glenn Maynard)
List: pgsql-performance

Tree view

Nested query performance issue  (Glenn Maynard, )
 Re: Nested query performance issue  (Віталій Тимчишин, )
  Re: Nested query performance issue  (Glenn Maynard, )
   Re: Nested query performance issue  (Віталій Тимчишин, )
    Re: Nested query performance issue  (Glenn Maynard, )
    Re: Nested query performance issue  (Greg Smith, )
    Re: Nested query performance issue  (Merlin Moncure, )
 Re: Nested query performance issue  (Heikki Linnakangas, )
  Re: Nested query performance issue  (Glenn Maynard, )
   Re: Nested query performance issue  (Matthew Wakeling, )
    Re: Nested query performance issue  (Glenn Maynard, )
  Re: Nested query performance issue  (Glenn Maynard, )
   Re: Nested query performance issue  (Tom Lane, )

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


pgsql-performance by date:

From: Merlin Moncure
Date:
Subject: Re: Nested query performance issue
From: Stephen Frost
Date:
Subject: Re: INSERT times - same storage space but more fields -> much slower inserts