To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: Query performance with disabled hashjoin and mergejoin
Date: Sat, 05 Feb 2011 03:50:50 +0100
Lines: 28
Message-ID: <iiiduc$jt0$1@dough.gmane.org>
References: <iigq7o$3ub$1@dough.gmane.org> <4D4C10EA.2080901@2ndquadrant.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US;
	rv:1.9.2.12) Gecko/20101102 Thunderbird/3.1.6
In-Reply-To: <4D4C10EA.2080901@2ndquadrant.com>

On 04/02/2011 15:44, Greg Smith wrote:
> Ivan Voras wrote:
>> The "vanilla" plan, with default settings is:
>
> Pause here for a second: why default settings? A default PostgreSQL
> configuration is suitable for systems with about 128MB of RAM. Since you
> say you have "good enough hardware", I'm assuming you have a bit more
> than that. The first things to try here are the list at
> http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ; your bad
> query here looks like it might benefit from a large increase to
> effective_cache_size, and possibly an increase to work_mem as well. Your
> "bad" plan here is doing a lot of sequential scans instead of indexed
> lookups, which makes me wonder if the change in join types you're
> forcing isn't fixing that part as a coincidence.

My earlier message didn't get through so here's a repeat:

Sorry for the confusion, by "default settings" I meant "planner default 
settings" not generic shared buffers, wal logs, work memory etc. - which 
are adequately tuned.

> Note that the estimated number of rows coming out of each form of plan
> is off by a factor of about 200X, so it's not that the other plan type
> is better estimating anything.

Any ideas how to fix the estimates? Or will I have to simulate hints by 
issuing "set enable_hashjoin=f; set enable_mergejoin=f;" for this query? :)