Re: Questions on query planner, join types, and work_mem

От: Tom Lane
Тема: Re: Questions on query planner, join types, and work_mem
Дата: ,
Msg-id: 25534.1280498632@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Questions on query planner, join types, and work_mem  (Peter Hussey)
Ответы: Re: Questions on query planner, join types, and work_mem  (Peter Hussey)
Список: pgsql-performance

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

Questions on query planner, join types, and work_mem  (Peter Hussey, )
 Re: Questions on query planner, join types, and work_mem  (Andres Freund, )
 Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
  Re: Questions on query planner, join types, and work_mem  (Alvaro Herrera, )
   Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
 Re: Questions on query planner, join types, and work_mem  (Jayadevan M, )
 Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
  Re: Questions on query planner, join types, and work_mem  (Peter Hussey, )
   Re: Questions on query planner, join types, and work_mem  (Robert Haas, )
   Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
   Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
    Re: Questions on query planner, join types, and work_mem  (Robert Haas, )
     Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
      Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
       Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
        Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
         Re: Questions on query planner, join types, and work_mem  (Greg Smith, )
          Re: Questions on query planner, join types, and work_mem  (Greg Smith, )
          Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
           Re: Questions on query planner, join types, and work_mem  (Greg Smith, )
            Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
          Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
         Re: Questions on query planner, join types, and work_mem  (Tom Lane, )
         Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
          Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
         Re: Questions on query planner, join types, and work_mem  (Hannu Krosing, )
       Re: Questions on query planner, join types, and work_mem  (Bruce Momjian, )
        Re: Questions on query planner, join types, and work_mem  (Robert Haas, )
         Re: Questions on query planner, join types, and work_mem  (Bruce Momjian, )
          Re: Questions on query planner, join types, and work_mem  (Bruce Momjian, )
   Re: Questions on query planner, join types, and work_mem  ("Kevin Grittner", )
 Re: Questions on query planner, join types, and work_mem  (Greg Smith, )

Peter Hussey <> writes:
> Using the default of 1MB work_mem, the planner chooses a hash join plan :
> "Hash Left Join  (cost=252641.82..11847353.87 rows=971572 width=111) (actual
> time=124196.670..280461.604 rows=968080 loops=1)"
> ...
> For the same default 1MB work_mem, a nested loop plan is better
> "Nested Loop Left Join  (cost=8.27..15275401.19 rows=971572 width=111)
> (actual time=145.015..189957.023 rows=968080 loops=1)"
> ...

Hm.  A nestloop with nearly a million rows on the outside is pretty
scary.  The fact that you aren't unhappy with that version of the plan,
rather than the hash, indicates that the "object" table must be
fully cached in memory, otherwise the repeated indexscans would be a
lot slower than this:

> "  ->  Index Scan using uq_object on object obj  (cost=0.00..3.51 rows=1
> width=95) (actual time=0.168..0.170 rows=1 loops=968080)"
> "        Index Cond: ((sd.lsid)::text = (obj.objecturi)::text)"

My take on it is that the estimate of the hash plan's cost isn't bad;
what's bad is that the planner is mistakenly estimating the nestloop as
being worse.  What you need to do is adjust the planner's cost
parameters so that it has a better idea of the true cost of repeated
index probes in your environment.  Crank up effective_cache_size if
you didn't already, and experiment with lowering random_page_cost.
See the list archives for more discussion of these parameters.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Karl Denninger
Дата:
Сообщение: Re: Testing Sandforce SSD
От: Robert Haas
Дата:
Сообщение: Re: Explains of queries to partitioned tables