RE: Slow query with aggregate and many LEFT JOINS

From: kimaidou [mailto:kimai...@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.

RE: Slow query with aggregate and many LEFT JOINS

From: kimaidou [mailto:kimai...@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 mater

Re: Aggregate and many LEFT JOIN

Michael Lewis writes: > Does the plan change significantly with this- > set session work_mem='250MB'; > set session geqo_threshold = 20; > set session join_collapse_limit = 20; Yeah ... by my count there are 16 tables in this query, so raising join_collapse_limit to 15 is not enough to ensure tha

Re: Aggregate and many LEFT JOIN

Does the plan change significantly with this- set session work_mem='250MB'; set session geqo_threshold = 20; set session join_collapse_limit = 20; With that expensive sort spilling to disk and then aggregating after that, it would seem like the work_mem being significantly increased is going to m

Re: Aggregate and many LEFT JOIN

Thanks for your answers. I have tried via --show work_mem; "10485kB" -> initial work_mem for my first post -- set session work_mem='10kB'; -- set session geqo_threshold = 12; -- set session join_collapse_limit = 15; I have a small machine, with SSD disk and 8GB RAM. I cannot really increase

Re: Aggregate and many LEFT JOIN

Curious- Is geqo_threshold still set to 12? Is increasing join_collapse_limit to be higher than geqo_threshold going to have a noticeable impact? The disk sorts are the killer as Justin says. I wonder how it performs with that increased significantly. Is the storage SSD or traditional hard disks?

Re: Aggregate and many LEFT JOIN

On Fri, Feb 22, 2019 at 04:14:05PM +0100, kimaidou wrote: > 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 This is writing 2GB tempfile, perhaps the query would benefit from larger work_m

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 ki

Aggregate and many LEFT JOIN

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 ki