Re: No hash join across partitioned tables?

On Thu, 16 Apr 2009, Kris Jurka wrote:

> Perhaps the cost estimates for the real data are so high because of this 
> bogus row count that the fudge factor to disable mergejoin isn't enough?
>

Indeed, I get these cost estimates on 8.4b1 with an increased 
disable_cost value:

nestloop:  11171206.18
merge:     58377401.39
hash:     116763544.76

So the default disable_cost isn't enough to push it to use the hash join 
plan and goes back to nestloop.  Since disable_cost hasn't been touched 
since January 2000, perhaps it's time to bump that up to match today's 
hardware and problem sizes?  This isn't even a particularly big problem, 
it's joing 18M rows against 30k.

The real problem is getting reasonable stats to pass through the partition 
Append step, so it can make a reasonable estimate of the join output size.

Kris Jurka