RE: Slow query with aggregate and many LEFT JOINS

From: Igor Neyman <ineyman(at)perceptron(dot)com>
To: Igor Neyman <ineyman(at)perceptron(dot)com>, kimaidou <kimaidou(at)gmail(dot)com>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: RE: Slow query with aggregate and many LEFT JOINS
Date: 2019-02-22 19:53:51
Message-ID: DM5PR17MB15323353E29926EE1BC19B04DA7F0@DM5PR17MB1532.namprd17.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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<mailto: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

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Gunther 2019-02-23 21:05:51 Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.
Previous Message Igor Neyman 2019-02-22 19:47:45 RE: Slow query with aggregate and many LEFT JOINS