From: Willy-Bas Loos <willybas@gmail.com>
To: pgsql-performance@postgresql.org
Subject: not using partial index
Date: Wed, 20 Apr 2011 10:46:59 +0200
Message-ID: <BANLkTikUt403PkMGb4bFMHMBZsJZRPV5Bw@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: willybas@gmail.com
Subject: Re: not using partial index
In-Reply-To: <BANLkTikUt403PkMGb4bFMHMBZsJZRPV5Bw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox