Postgres Professional
en

Re: Nested query performance issue

От: Matthew Wakeling
Тема: Re: Nested query performance issue
Дата: ,
Msg-id: alpine.DEB.2.00.0904141029500.4053@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответ на: Re: Nested query performance issue  (Glenn Maynard)
Ответы: Re: Nested query performance issue  (Glenn Maynard)
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

Предыдущее:
От: Merlin Moncure
Дата:
Сообщение: Re: Nested query performance issue
Следующее:
От: Stephen Frost
Дата:
Сообщение: Re: INSERT times - same storage space but more fields -> much slower inserts