Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux