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

On 7/26/05, Csaba Nagy <nagy(at)ecircle-ag(dot)com> wrote:
> Hi all,
>
> Jumping in directly to the subject, this is what I get:
>
> explain SELECT bigint_col_1, bigint_col_2 FROM big_table WHERE
> bigint_col_2 in (12132131, null, null, null,
> null);
>
> QUERY PLAN
> -------------------------------------------------------------------
> Seq Scan on big_table (cost=0.00..2447201.85 rows=448 width=16)
> Filter: ((bigint_col_2 = 12132131::bigint) OR NULL::boolean)
> (2 rows)
>
>

this is because null values can't be indexed... or telling other way
the planner will never choose an index for comparing to null

maybe a partial index can be used?

> Compared to:
>
>
> explain SELECT bigint_col_1, bigint_col_2 FROM big_table WHERE
> bigint_col_2 in (12132131, 123781, 1297839032, 123667123);
>
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using dom_idx_member_bigint_col_2,
> dom_idx_member_bigint_col_2, dom_idx_member_bigint_col_2,
> dom_idx_member_bigint_col_2 on big_table (cost=0.00..6427.28 rows=1789
> width=16)
> Index Cond: ((bigint_col_2 = 12132131) OR (bigint_col_2 = 123781) OR
> (bigint_col_2 = 1297839032) OR (bigint_col_2 = 123667123))
> (2 rows)
>
>
> big_table has ~ 100 million rows.
>

there is no nulls here so the index can be used

>
> Considering that NULL::boolean is always false,
>

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

template1=# select null::boolean;
bool
------

(1 fila)

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)