Aggregate and many LEFT JOIN

[kimaidou <kimaidou@xxxxxxxxx>]

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