From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Tobias Brox <tobias@nordicbet.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Merge Join vs Nested Loop
Date: Wed, 27 Sep 2006 10:30:59 -0500
Message-ID: <1159371059.26848.82.camel@state.g2switchworks.com> (raw)
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smarlowe@g2switchworks.com, tobias@nordicbet.com, tgl@sss.pgh.pa.us
Subject: Re: Merge Join vs Nested Loop
In-Reply-To: <1159371059.26848.82.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox