Re: [PERFORM] Slow query with 3 table joins

От: Gerardo Herzig
Тема: Re: [PERFORM] Slow query with 3 table joins
Дата: ,
Msg-id: 141643966.192926.1493209639777.JavaMail.root@fmed.uba.ar
(см: обсуждение, исходный текст)
Ответ на: Re: [PERFORM] Slow query with 3 table joins  (Alessandro Ferrucci)
Список: pgsql-performance

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

Re: [PERFORM] Slow query with 3 table joins  (Alessandro Ferrucci, )
 Re: [PERFORM] Slow query with 3 table joins  (David Rowley, )
  Re: [PERFORM] Slow query with 3 table joins  (Alessandro Ferrucci, )
  Re: [PERFORM] Slow query with 3 table joins  (Alessandro Ferrucci, )
 Re: [PERFORM] Slow query with 3 table joins  (Johan Fredriksson, )
  Re: [PERFORM] Slow query with 3 table joins  (Alessandro Ferrucci, )
   Re: [PERFORM] Slow query with 3 table joins  (Johan Fredriksson, )
 Re: [PERFORM] Slow query with 3 table joins  (Gerardo Herzig, )
 Re: [PERFORM] Slow query with 3 table joins  (Gerardo Herzig, )
  Re: [PERFORM] Slow query with 3 table joins  (Matthew Bellew, )


----- Mensaje original -----
> De: "Alessandro Ferrucci" <>
> Para: 
> Enviados: Miércoles, 26 de Abril 2017 0:19:37
> Asunto: Re: [PERFORM] Slow query with 3 table joins
>
>
>
> After about 40 inutes the slow query finally finished and the result
> of the EXPLAIN plan can be found here:
>
>
> https://explain.depesz.com/s/BX22
>
>
> Thanks,
> Alessandro Ferrucci

1) Looking at the "Rows removed by filter" in that explain, looks like a selectivity issue: Many (many many) rows are
fetched,just to be rejected later.  
I think you can try a partial index on ''field (unit_id) where field_name="SHEETS_PRESENT"'', if it is practical to
you.
See https://www.postgresql.org/docs/current/static/indexes-partial.html for a good read about partial indexes.

2) 9.2 is a pretty old version of PG. If you are migrating yet, you should consider a more recent version

HTH

Gerardo



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

От: Matthew Bellew
Дата:
Сообщение: Re: [PERFORM] Slow query with 3 table joins
От: Alexey Vasiliev
Дата:
Сообщение: [PERFORM] EXCLUDE CONSTRAINT with intarray