[PERFORM] not using partial index

[Willy-Bas Loos]

Hi,

I'm using PostgreSQL 8.4 (and also 8.3).

A partial index like this:
CREATE INDEX table2_field1_idx
  ON table2 (field1)
 WHERE NOT field1 ISNULL;

Will not be used when select one record from 100K records:

explain select * from table2 where field1 = 256988
'Seq Scan on table2  (cost=0.00..1693.01 rows=1 width=4)'
'  Filter: (field1 = 256988)'

But it WILL be used like this:

explain select * from table2 where field1 = 256988 and not field1 isnull
'Index Scan using table2_field1_idx on table2  (cost=0.00..8.28 rows=1
width=4)'
'  Index Cond: (field1 = 256988)'


But, when i change the index from"NOT field1 ISNULL " to "field1 NOTNULL",
then the index WILL be used in both queries:

explain select * from table1 where field1 = 256988
'Index Scan using table1_field1_idx on table1  (cost=0.00..8.28 rows=1
width=4)'
'  Index Cond: (field1 = 256988)'

'Index Scan using table1_field1_idx on table1  (cost=0.00..8.28 rows=1
width=4)'
'  Index Cond: (field1 = 256988)'
'  Filter: (NOT (field1 IS NULL))'


Any ideas why this might be?


Cheers,

WBL

Code below:

--drop table table1;
create table table1(field1 integer);
CREATE INDEX table1_field1_idx
  ON table1 (field1)
  WHERE field1 NOTNULL;
insert into table1 values(null);
insert into table1 select generate_series(1,100000);

vacuum analyze table1;

explain select * from table1 where field1 = 256988
explain select * from table1 where field1 = 256988 and not field1 isnull


--drop table table2;
create table table2(field1 integer);
CREATE INDEX table2_field1_idx
  ON table2 (field1)
  WHERE NOT field1 ISNULL;
insert into table2 values(null);
insert into table2 select generate_series(1,100000);

vacuum analyze table2;

explain select * from table2 where field1 = 256988
explain select * from table2 where field1 = 256988 and not field1 isnull


-- 
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw