Re: Slow SQL query (14-15 seconds)

От: Tomasz Myrta
Тема: Re: Slow SQL query (14-15 seconds)
Дата: ,
Msg-id: gfh9am$2as3$1@news.hub.org
(см: обсуждение, исходный текст)
Ответ на: 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 napisal 13.11.2008 12:02:
> Hello !
>
> Sorry for the subject, I didn't found a better one ! :-/
>
> 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.
>
> If the lines are too long, your mailreader may cut them and make the SQL
> query and the query plan unreadable, so I've put a copy of them on
> pastebin.com : <http://pastebin.com/m53ca365>
>
> Can you give me some tips to see which part of the query is guilty ?

1. Your explain analyze points to a lot of loops in exists clause:

Filter: ((NOT is_deleted) AND (subplan))
16.5msec * 800loops = ~13sec.

Try to replace exists() with in() or inner joins/distinct.

2. Those 3 left joins can be replaced with subselects:
select (select count(*)... ) as societe_nbre_commandes
from societes ...

--
Regards,
Tomasz Myrta


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

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