Willy-Bas Loos <willybas@xxxxxxxxx> wrote: >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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
