Re: No hash join across partitioned tables?

[Bruce Momjian <bruce(at)momjian(dot)us>]

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. +