Обсуждение: not using partial index

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

Willy-Bas Loos <willybas@gmail.com> writes:
> [ NOT field1 ISNULL is not seen as equivalent to field1 IS NOT NULL ]

> Any ideas why this might be?

The planner does not spend an infinite number of cycles on trying to
make different expressions look alike.

As it happens, 9.1 does know this equivalence, as a byproduct of
http://git.postgresql.org/gitweb?p=postgresql.git&a=commitdiff&h=220e45bf325b061b8dbd7451f87cedc07da61706
But I don't consider it a bug that older versions don't do it.

            regards, tom lane

Willy-Bas Loos <willybas@gmail.com> 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