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: <20060927152655.GF17834@oppetid.no>
References: <20060926193553.GA27268@oppetid.no>
	<719.1159308596@sss.pgh.pa.us> <20060927094803.GA11245@oppetid.no>
	<1159369111.4643.0.camel@localhost.localdomain>
	<20060927150512.GE17834@oppetid.no>
	<1159370365.4643.7.camel@localhost.localdomain>
	<20060927152655.GF17834@oppetid.no>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1159371059.26848.82.camel@state.g2switchworks.com>
Mime-Version: 1.0
Date: Wed, 27 Sep 2006 10:30:59 -0500

On Wed, 2006-09-27 at 10:26, Tobias Brox wrote:
> [Scott Marlowe - Wed at 10:19:24AM -0500]
> > So, by decreasing them, you should move away from nested loops then,
> > right?  Has that not worked for some reason?
> 
> I want to move to nested loops, they are empirically faster in many of
> our queries, and that makes sense since we've got quite big tables and
> most of the queries only touch a small partition of the data.
> 
> I've identified that moving any of the cost constants (including
> random_page_cost) upwards gives me the right result, but I'm still wary
> if this is the right thing to do.  Even if so, what constants should I
> target first?  I could of course try to analyze a bit what constants
> give the biggest impact.  Then again, we have many more queries hitting
> the database than the few I'm doing research into (and those I'm doing
> research into is even very simplified versions of the real queries).

Ahh, the other direction then.  I would think it's safer to nudge these
a bit than to drop random page cost to 1 or set effective_cache_size to
1000 etc...

But I'm sure you should test the other queries and / or keep an eye on
your database while running to make sure those changes don't impact
other users.