Re: Query with large number of joins

-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Marco Di Cesare
Sent: Tuesday, October 21, 2014 4:03 PM
To: Andrew Dunstan; Merlin Moncure
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Query with large number of joins


On 10/21/2014 12:31 PM, Andrew Dunstan wrote:
> Please don't top-post on the PostgreSQL lists. See 
> 

Oops, sorry.

>Have you tried a) either turning off geqo or setting geqo_threshold 
>fairly high b) setting join_collapse_limit fairly high (assuming all the above join targets are tables and not views, setting it to something like 25 should do the trick.

I did try various combinations of these settings but none yielded any significant query run time improvements. 

> You also haven't told us what settings you have for things like effective_cache_size, which can dramatically affect query plans.

effective_cache_size = 4096MB

I tried bumping this up as well but again no significant query run time improvements.  



Marco,

Didn't you mention, that you have something like 48GB RAM?
In this case (if that's dedicated db server), you should try and set effective_cache_size around 40GB (not 4GB).

Regards,
Igor Neyman