Skip site navigation (1) Skip section navigation (2)

Re: No hash join across partitioned tables?

From: Kris Jurka <books(at)ejurka(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: No hash join across partitioned tables?
Date: 2009-04-17 01:02:04
Message-ID: Pine.BSO.4.64.0904162046050.27034@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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


In response to

Responses

pgsql-performance by date

Next:From: Craig RingerDate: 2009-04-17 01:22:08
Subject: Re: GiST index performance
Previous:From: Francisco Figueiredo Jr.Date: 2009-04-16 23:43:20
Subject: Re: need information

pgsql-hackers by date

Next:From: Robert HaasDate: 2009-04-17 01:43:11
Subject: Re: HashJoin w/option to unique-ify inner rel
Previous:From: Robert HaasDate: 2009-04-17 00:28:43
Subject: Re: HashJoin w/option to unique-ify inner rel

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group