RE: Slow query with aggregate and many LEFT JOINS

Поиск
Список
Период
Сортировка
От Igor Neyman
Тема RE: Slow query with aggregate and many LEFT JOINS
Дата
Msg-id DM5PR17MB15323353E29926EE1BC19B04DA7F0@DM5PR17MB1532.namprd17.prod.outlook.com
обсуждение исходный текст
Ответ на RE: Slow query with aggregate and many LEFT JOINS  (Igor Neyman <ineyman@perceptron.com>)
Список pgsql-performance
Дерево обсуждения

From: kimaidou [mailto:kimaidou@gmail.com]
Sent: Friday, February 22, 2019 10:37 AM
To: pgsql-performance@lists.postgresql.org
Subject: Slow query with aggregate and many LEFT JOINS

 

Hi all,

I need to optimize the following query
http://paste.debian.net/hidden/ef08f864/
I use it to create  a materialized view, but I think there is room for optimization.
I tried to
SET join_collapse_limit TO 15;
with to real difference.

Explain shows that the GROUP AGGREGATE and needed sort kill the performance.
Do you have any hint how to optimize this ?
https://explain.depesz.com/s/6nf

Regards
Michaël

 

Don’t know your hardware config, or Postgres settings,

but I see external disk sort.  So, try setting work_mem to ~48MB.

 

Regards,

Igor Neyman

 

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

Предыдущее
От: Igor Neyman
Дата:
Сообщение: RE: Slow query with aggregate and many LEFT JOINS
Следующее
От: Gunther
Дата:
Сообщение: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.