On 7/26/05, Csaba Nagy <nagy@xxxxxxxxxxxxxx> 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 ;)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
