Re: Planner choosing NestedLoop, although it is slower...

От: Tom Lane
Тема: Re: Planner choosing NestedLoop, although it is slower...
Дата: ,
Msg-id: 4626.1310501052@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Planner choosing NestedLoop, although it is slower...  (Mario Splivalo)
Ответы: Re: Planner choosing NestedLoop, although it is slower...  (Mario Splivalo)
Список: pgsql-performance

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

Planner choosing NestedLoop, although it is slower...  (Mario Splivalo, )
 Re: Planner choosing NestedLoop, although it is slower...  (Tom Lane, )
  Re: Planner choosing NestedLoop, although it is slower...  (Mario Splivalo, )
   Re: Planner choosing NestedLoop, although it is slower...  (Tom Lane, )
    Re: Planner choosing NestedLoop, although it is slower...  (Mario Splivalo, )
    Re: Planner choosing NestedLoop, although it is slower...  (Mario Splivalo, )
     Re: Planner choosing NestedLoop, although it is slower...  (Mario Splivalo, )
 Re: Planner choosing NestedLoop, although it is slower...  (Clem Dickey, )

Mario Splivalo <> writes:
>   Limit  (cost=0.00..415.91 rows=21 width=8) (actual
> time=11263.089..11263.089 rows=0 loops=1)
>     ->  Nested Loop  (cost=0.00..186249.55 rows=9404 width=8) (actual
> time=11263.087..11263.087 rows=0 loops=1)

> Why is planner using NestedLoops,

Because it thinks the LIMIT will kick in and end the query when the join
is only 21/9404ths (ie, a fraction of a percent) complete.  A NestLoop
results in saving a lot of work in that situation, whereas hash-and-sort
has to do the whole join despite the LIMIT.

What you need to look into is why the estimated join size is 9400 rows
when the actual join size is zero.  Are both tables ANALYZEd?  Are you
intentionally selecting rows that have no join partners?

            regards, tom lane


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

От: Tom Lane
Дата:
Сообщение: Re: UPDATEDs slowing SELECTs in a fully cached database
От: Mario Splivalo
Дата:
Сообщение: Re: Planner choosing NestedLoop, although it is slower...