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.0904161921080.11937@leary.csoft.net
(view: Whole thread, Raw)
In response to: Re: No hash join across partitioned tables?  (Tom Lane)
Responses: Re: No hash join across partitioned tables?  (Kris Jurka)
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, Tom Lane wrote:

> Kris Jurka <> 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

pgsql-performance by date:

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