Re: Slow SQL query (14-15 seconds)

От: Tom Lane
Тема: Re: Slow SQL query (14-15 seconds)
Дата: ,
Msg-id: 14560.1226586608@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Slow SQL query (14-15 seconds)  (Bruno Baguette)
Список: pgsql-performance

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

Slow SQL query (14-15 seconds)  (Bruno Baguette, )
 Re: Slow SQL query (14-15 seconds)  (Matthew Wakeling, )
  Re: Slow SQL query (14-15 seconds)  (Bruno Baguette, )
 Re: Slow SQL query (14-15 seconds)  (Tom Lane, )
  Re: Slow SQL query (14-15 seconds)  (Bruno Baguette, )
   Re: Slow SQL query (14-15 seconds)  (Tom Lane, )
 Re: Slow SQL query (14-15 seconds)  (Tomasz Myrta, )
 Re: Slow SQL query (14-15 seconds)  ("Vladimir Sitnikov", )
  Re: Slow SQL query (14-15 seconds)  (Bruno Baguette, )

Bruno Baguette <> writes:
> Le 13/11/08 14:31, Tom Lane a �crit :
>> 16.511 * 818 = 13505.998, so this is all but about 100 msec of the
>> runtime.  Can't tell if there's any easy way to improve it.  In
>> pre-8.4 releases trying to convert the EXISTS into an IN might help.

> Can you explain why a IN is fastest than an EXISTS subquery ?

The planner is smarter about IN than EXISTS --- it can usually convert
the former into a join plan instead of a subplan.  (This situation will
improve in 8.4.)

> Do you think I can improve again the performance of that query ?

You've still got a subplan in there, not quite sure why.  Anyway,
increasing work_mem might get it to change to a hashed subplan,
which'd likely be faster.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Bruno Baguette
Дата:
Сообщение: Re: Slow SQL query (14-15 seconds)
От: "Jeremiah Elliott"
Дата:
Сообщение: crosstab speed