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-16 23:30:51
Message-ID: Pine.BSO.4.64.0904161921080.11937@leary.csoft.net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance

On Thu, 16 Apr 2009, Tom Lane wrote:

> Kris Jurka <books(at)ejurka(dot)com> writes:
>> PG (8.3.7) doesn't seem to want to do a hash join across two partitioned
>> tables.
>
> Could we see the whole declaration of these tables?  (pg_dump -s output
> would be convenient)
>

The attached table definition with no data wants to mergejoin first, but 
after disabling mergejoin it does indeed do a hashjoin.

Looking back at the cost estimates for the merge and nestloop joins, it 
seems to be selecting the number of rows in the cartesian product * .005 
while the number of output rows in this case is 2437 (cartesian product * 
4e-9).  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?

Kris Jurka

Attachment: hash-join-partition.sql
Description: text/plain (2.5 KB)

In response to

Responses

pgsql-performance by date

Next:From: Francisco Figueiredo Jr.Date: 2009-04-16 23:43:20
Subject: Re: need information
Previous:From: Tom LaneDate: 2009-04-16 23:12:11
Subject: Re: No hash join across partitioned tables?

pgsql-hackers by date

Next:From: Robert HaasDate: 2009-04-17 00:28:43
Subject: Re: HashJoin w/option to unique-ify inner rel
Previous:From: Tom LaneDate: 2009-04-16 23:26:52
Subject: Re: HashJoin w/option to unique-ify inner rel

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