Re: No hash join across partitioned tables?

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kris Jurka <books(at)ejurka(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: No hash join across partitioned tables?
Date: 2010-02-25 23:46:33
Message-ID: 201002252346.o1PNkXG07892@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance


Did this get addressed?

---------------------------------------------------------------------------

Tom Lane wrote:
> Kris Jurka <books(at)ejurka(dot)com> writes:
> > 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.
>
> I dug around a bit and concluded that the lack of stats for the Append
> relation is indeed the main problem. It's not so much the bad join size
> estimate (although that could hurt for cases where you need to join this
> result to another table). Rather, it's that the planner is deliberately
> biased against picking hash joins in the absence of stats for the inner
> relation. Per the comments for estimate_hash_bucketsize:
>
> * If no statistics are available, use a default estimate of 0.1. This will
> * discourage use of a hash rather strongly if the inner relation is large,
> * which is what we want. We do not want to hash unless we know that the
> * inner rel is well-dispersed (or the alternatives seem much worse).
>
> While we could back off the default a bit here, I think it'd be better
> to fix it by not punting on the stats-for-append-relations problem.
> That doesn't seem like material for 8.4 at this point, though.
>
> regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2010-02-25 23:46:53 Re: trouble with to_char('L')
Previous Message Greg Stark 2010-02-25 23:45:03 Re: A thought on Index Organized Tables

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-02-26 00:03:34 Re: No hash join across partitioned tables?
Previous Message Bruce Momjian 2010-02-25 23:44:02 Re: GiST index performance