Re: No hash join across partitioned tables? - Mailing list pgsql-performance

From Kris Jurka
Subject Re: No hash join across partitioned tables?
Date
Msg-id Pine.BSO.4.64.0904162046050.27034@leary.csoft.net
Whole thread Raw
In response to Re: No hash join across partitioned tables?  (Kris Jurka <books@ejurka.com>)
Responses Re: No hash join across partitioned tables?
Re: No hash join across partitioned tables?
List pgsql-performance

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


pgsql-performance by date:

Previous
From: Kris Jurka
Date:
Subject: Re: No hash join across partitioned tables?
Next
From: Craig Ringer
Date:
Subject: Re: GiST index performance