Re: Bad plan when null is in an "in" list

Jaime Casanova <systemguards(at)gmail(dot)com> writes:
> On 7/26/05, Csaba Nagy <nagy(at)ecircle-ag(dot)com> wrote:
>> Seq Scan on big_table (cost=0.00..2447201.85 rows=448 width=16)
>> Filter: ((bigint_col_2 = 12132131::bigint) OR NULL::boolean)
>>
>> Considering that NULL::boolean is always false,

> null::boolean is null not false. that is because null means 'unknown
> value' not false nor true

The reason the planner ends up with this and not just "bigint_col_2 =
12132131::bigint" is that it's using a general-purpose expression
simplifier, and in the general case we have to keep the NULL arm of
the OR because it can affect the result (the OR output will be NULL
not FALSE if the equality is false).

In the context of the top level of a WHERE clause, we could discard the
NULL, and then the OR, since we do not need to distinguish NULL and
FALSE results. However, as far as I can see doing this would require an
extra pass over the WHERE clause (it can't readily be folded into any of
the existing traversals because those are done by routines that have
other uses where dropping NULLs would be wrong). I'm unconvinced that
the use-case for this justifies that much overhead ...

regards, tom lane