Re: *very* inefficient choice made by the planner (regarding

От: Tom Lane
Тема: Re: *very* inefficient choice made by the planner (regarding
Дата: ,
Msg-id: 20172.1087568965@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: *very* inefficient choice made by the planner (regarding  (SZUCS Gábor)
Список: pgsql-performance

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

*very* inefficient choice made by the planner (regarding IN(...))  (Frank van Vugt, )
 Re: *very* inefficient choice made by the planner (regarding  (Stephan Szabo, )
 Re: *very* inefficient choice made by the planner (regarding IN(...))  (Tom Lane, )
  Re: *very* inefficient choice made by the planner (regarding IN(...))  (Frank van Vugt, )
 Re: *very* inefficient choice made by the planner (regarding  (Jean-Luc Lachance, )
  Re: *very* inefficient choice made by the planner (regarding  (Tom Lane, )
   Re: *very* inefficient choice made by the planner (regarding  (Jean-Luc Lachance, )
    Re: *very* inefficient choice made by the planner (regarding  (Stephan Szabo, )
     Re: *very* inefficient choice made by the planner (regarding  (Stephan Szabo, )
 Re: *very* inefficient choice made by the planner (regarding  (SZUCS Gábor, )
  Re: *very* inefficient choice made by the planner (regarding  (Tom Lane, )
  Re: *very* inefficient choice made by the planner (regarding  (Stephan Szabo, )

=?iso-8859-1?Q?SZUCS_G=E1bor?= <> writes:
>> It's NULLs inside the subselect that are the issue.
>
> select 1 in (select a from foo)
> select exists ( select 1 from foo where a=1)

> Just a dumb try :)

>   SELECT (exists(select 1 from foo where a isnull) AND NULL)
>        OR exists(select 1 from foo where a=1)

The more general case is where you have a variable (or expression)
on the left of IN.  That could be NULL too, and this still doesn't
give the right result in that case :-(.  With NULL on the left the
correct answer would be FALSE if the subselect has zero rows,
NULL otherwise.

            regards, tom lane


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

От: "Joshua D. Drake"
Дата:
Сообщение: Re: [BULK] Problems with vacuum!
От: Tom Lane
Дата:
Сообщение: Re: Major differences between oracle and postgres performance - what can I do ?