Re: Merge Join vs Nested Loop

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.