Re: No hash join across partitioned tables?

From: Kris Jurka
Subject: Re: No hash join across partitioned tables?
Date: ,
Msg-id: Pine.BSO.4.64.0904162046050.27034@leary.csoft.net
(view: Whole thread, Raw)
In response to: Re: No hash join across partitioned tables?  (Kris Jurka)
Responses: Re: No hash join across partitioned tables?  (Tom Lane)
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, )


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



pgsql-performance by date:

From: Vlad Arkhipov
Date:
Subject: Optimizer's issue
From: Tom Lane
Date:
Subject: Re: No hash join across partitioned tables?