Re: No hash join across partitioned tables?

От: Kris Jurka
Тема: Re: No hash join across partitioned tables?
Дата: ,
Msg-id: Pine.BSO.4.64.0904162046050.27034@leary.csoft.net
(см: обсуждение, исходный текст)
Ответ на: 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?  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

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 по дате сообщения:

От: Vlad Arkhipov
Дата:
Сообщение: Optimizer's issue
От: Tom Lane
Дата:
Сообщение: Re: No hash join across partitioned tables?