RE: Slow query with aggregate and many LEFT JOINS

From: kimaidou [mailto:kimaidou(at)gmail(dot)com]
Sent: Friday, February 22, 2019 10:37 AM
To: pgsql-performance(at)lists(dot)postgresql(dot)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

Try increasing both: join_collapse_limit and from_collapse_limit to 16 (or even 17).

Regards,
Igor Neyman