Re: Merge Join vs Nested Loop

[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? :-)