Message-ID: <4D4C10EA.2080901@2ndquadrant.com>
Date: Fri, 04 Feb 2011 09:44:58 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Ivan Voras <ivoras@freebsd.org>
CC: pgsql-performance@postgresql.org
Subject: Re: Query performance with disabled hashjoin and mergejoin
References: <iigq7o$3ub$1@dough.gmane.org>
In-Reply-To: <iigq7o$3ub$1@dough.gmane.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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.

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.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books