Skip site navigation (1) Skip section navigation (2)

Re: Merge Join vs Nested Loop

From: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
To: Tobias Brox <tobias(at)nordicbet(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Merge Join vs Nested Loop
Date: 2006-09-27 14:58:30
Message-ID: 1159369111.4643.0.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-performance
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?

In response to

Responses

pgsql-performance by date

Next:From: Tobias BroxDate: 2006-09-27 15:05:12
Subject: Re: Merge Join vs Nested Loop
Previous:From: Jochem van DietenDate: 2006-09-27 12:12:18
Subject: Re: Forcing the use of particular execution plans

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group