Re: Slow SQL query (14-15 seconds)

Список
Период
Сортировка
От Tom Lane
Тема Re: Slow SQL query (14-15 seconds)
Дата
Msg-id 5163.1226583110@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Slow SQL query (14-15 seconds)  (Bruno Baguette)
Ответы 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:
> I'm having a problem with this query (below) that takes betweend 14 and
> 15 seconds to run, which is too long for the end-user.
> I've done a EXPLAIN ANALYZE (below below) but I'm having difficulties to
> see which part of that query is taking so many times.

It's the repeatedly executed EXISTS subplan that's hurting you:

>                             SubPlan
>                               ->  Nested Loop  (cost=35.56..378.16
> rows=2 width=0) (actual time=16.511..16.511 rows=0 loops=818)

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.

            regards, tom lane

В списке pgsql-performance по дате отправления:

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