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 15:30:59
Message-ID: 1159371059.26848.82.camel@state.g2switchworks.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

In response to

pgsql-performance by date

Next:From: Tobias BroxDate: 2006-09-27 15:36:19
Subject: Re: Merge Join vs Nested Loop
Previous:From: Tobias BroxDate: 2006-09-27 15:26:55
Subject: Re: Merge Join vs Nested Loop

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