Subject: Re: Merge Join vs Nested Loop
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Tobias Brox <tobias@nordicbet.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
In-Reply-To: <20060927094803.GA11245@oppetid.no>
References: <20060926193553.GA27268@oppetid.no>
	<719.1159308596@sss.pgh.pa.us>  <20060927094803.GA11245@oppetid.no>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Wed, 27 Sep 2006 09:58:30 -0500
Message-Id: <1159369111.4643.0.camel@localhost.localdomain>
Mime-Version: 1.0

On Wed, 2006-09-27 at 11:48 +0200, Tobias Brox wrote:
> [Tom Lane - Tue at 06:09:56PM -0400]
> > If your tables are small enough to fit (mostly) in memory, then the
> > planner tends to overestimate the cost of a nestloop because it fails to
> > account for cacheing effects across multiple scans of the inner table.
> > This is addressed in 8.2, but in earlier versions about all you can do
> > is reduce random_page_cost, and a sane setting of that (ie not less than
> > 1.0) may not be enough to push the cost estimates where you want them.
> > Still, reducing random_page_cost ought to be your first recourse.
> 
> Thank you.  Reducing the random page hit cost did reduce the nested loop
> cost significantly, sadly the merge join costs where reduced even
> further, causing the planner to favor those even more than before.
> Setting the effective_cache_size really low solved the issue, but I
> believe we rather want to have a high effective_cache_size.
> 
> Eventually, setting the effective_cache_size to near-0, and setting
> random_page_cost to 1 could maybe be a desperate measure.  Another one
> is to turn off merge/hash joins and seq scans.  It could be a worthwhile
> experiment if nothing else :-)
> 
> The bulk of our database is historical data that most often is not
> touched at all, though one never knows for sure until the queries have
> run all through - so table partitioning is not an option, it seems like.
> My general idea is that nested loops would cause the most recent data
> and most important part of the indexes to stay in the OS cache.  Does
> this make sense from an experts point of view? :-)

Have you tried chaning the cpu_* cost options to see how they affect
merge versus nested loop?