Re: No hash join across partitioned tables?

From: Bruce Momjian
Subject: Re: No hash join across partitioned tables?
Date: ,
Msg-id: 201002252346.o1PNkXG07892@momjian.us
(view: Whole thread, Raw)
In response to: Re: No hash join across partitioned tables?  (Tom Lane)
Responses: Re: No hash join across partitioned tables?  (Tom Lane)
List: pgsql-performance

Tree view

No hash join across partitioned tables?  (Kris Jurka, )
 Re: No hash join across partitioned tables?  (Tom Lane, )
  Re: No hash join across partitioned tables?  (Kris Jurka, )
   Re: No hash join across partitioned tables?  (Kris Jurka, )
    Re: No hash join across partitioned tables?  (Tom Lane, )
     Re: No hash join across partitioned tables?  (Kris Jurka, )
    Re: No hash join across partitioned tables?  (Tom Lane, )
     Re: No hash join across partitioned tables?  (Bruce Momjian, )
      Re: No hash join across partitioned tables?  (Tom Lane, )
       Re: No hash join across partitioned tables?  (Robert Haas, )
        Re: No hash join across partitioned tables?  (Tom Lane, )
         Re: No hash join across partitioned tables?  (Grzegorz Jaśkiewicz, )
         Re: No hash join across partitioned tables?  (Robert Haas, )
          Re: No hash join across partitioned tables?  (Tom Lane, )
           Re: No hash join across partitioned tables?  (Bruce Momjian, )
            Re: No hash join across partitioned tables?  (Tom Lane, )
             Re: No hash join across partitioned tables?  (Bruce Momjian, )
              Re: No hash join across partitioned tables?  (Tom Lane, )
               Re: No hash join across partitioned tables?  (Robert Haas, )
                Re: No hash join across partitioned tables?  (Bruce Momjian, )
          Re: No hash join across partitioned tables?  (Alvaro Herrera, )
           Re: No hash join across partitioned tables?  (Tom Lane, )
            Re: No hash join across partitioned tables?  (Samuel Gendler, )
             Re: No hash join across partitioned tables?  (Alvaro Herrera, )
              Re: No hash join across partitioned tables?  (Samuel Gendler, )
               Re: No hash join across partitioned tables?  (Alvaro Herrera, )
            Re: No hash join across partitioned tables?  (Robert Haas, )
 Re: No hash join across partitioned tables?  (Tom Lane, )
  Re: No hash join across partitioned tables?  (Kris Jurka, )

Did this get addressed?

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

Tom Lane wrote:
> Kris Jurka <> 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 ()
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

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


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: No hash join across partitioned tables?
From: Tory M Blue
Date:
Subject: bgwriter, checkpoints, curious (seeing delays)