DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=ZB/+Wyj5wB3+PY2edaJGaryUTVAxNG1jKK5uHdja2VH72mL7hysRcTgD+KbEkpIDK3
	F67N/b1q2+M/zbvd+YJLtKwa2+hl556S2OeZXD2l3ks+zivBnsi72z1w+FaWZSpShKGs
	NO1/uYOYMsV5z5fwbUR4HOLB70USxBjEo8m6c=
MIME-Version: 1.0
Date: Wed, 20 Apr 2011 10:46:59 +0200
Message-ID: <BANLkTikUt403PkMGb4bFMHMBZsJZRPV5Bw@mail.gmail.com>
Subject: not using partial index
From: Willy-Bas Loos <willybas@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=20cf307d01eef59a7704a155aa15

--20cf307d01eef59a7704a155aa15
Content-Type: text/plain; charset=ISO-8859-1

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

--20cf307d01eef59a7704a155aa15
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<div>Hi,</div><div><br></div><div>I&#39;m using PostgreSQL 8.4 (and also 8.=
3).</div><div><br></div><div>A partial index like this:</div><div><div>CREA=
TE INDEX table2_field1_idx</div><div>=A0=A0ON table2 (field1)</div></div><d=
iv>
=A0WHERE NOT field1 ISNULL;</div><div><br></div><div>Will not be used when =
select one record from 100K records:</div><div><br></div><div>explain selec=
t * from table2 where field1 =3D 256988</div><div><div>&#39;Seq Scan on tab=
le2 =A0(cost=3D0.00..1693.01 rows=3D1 width=3D4)&#39;</div>
<div>&#39; =A0Filter: (field1 =3D 256988)&#39;</div></div><div><br></div><d=
iv>But it WILL be used like this:</div><div><br></div><div>explain select *=
 from table2 where field1 =3D 256988 and not field1 isnull</div><div>&#39;I=
ndex Scan using table2_field1_idx on table2 =A0(cost=3D0.00..8.28 rows=3D1 =
width=3D4)&#39;</div>
<div>&#39; =A0Index Cond: (field1 =3D 256988)&#39;</div><div><br></div><div=
><br></div><div>But, when i change the index from&quot;NOT field1 ISNULL &q=
uot; to &quot;field1 NOTNULL&quot;, then the index WILL be used in both que=
ries:</div>
<div><br></div><div>explain select * from table1 where field1 =3D 256988</d=
iv><div><div>&#39;Index Scan using table1_field1_idx on table1 =A0(cost=3D0=
.00..8.28 rows=3D1 width=3D4)&#39;</div><div>&#39; =A0Index Cond: (field1 =
=3D 256988)&#39;</div>
</div><div><br></div><div><div>&#39;Index Scan using table1_field1_idx on t=
able1 =A0(cost=3D0.00..8.28 rows=3D1 width=3D4)&#39;</div><div>&#39; =A0Ind=
ex Cond: (field1 =3D 256988)&#39;</div><div>&#39; =A0Filter: (NOT (field1 I=
S NULL))&#39;</div>
</div><div><br></div><div><br></div><div>Any ideas why this might be?</div>=
<div><br></div><div><br></div><div>Cheers,</div><div><br></div><div>WBL</di=
v><div><br></div><div>Code below:</div><div><br></div><div><div>--drop tabl=
e table1;</div>
<div>create table table1(field1 integer);</div><div>CREATE INDEX table1_fie=
ld1_idx</div><div>=A0=A0ON table1 (field1)</div><div>=A0=A0WHERE field1 NOT=
NULL;</div><div>insert into table1 values(null);</div><div>insert into tabl=
e1 select generate_series(1,100000);</div>
<div><br></div><div>vacuum analyze table1;</div><div><br></div><div>explain=
 select * from table1 where field1 =3D 256988</div><div>explain select * fr=
om table1 where field1 =3D 256988 and not field1 isnull</div><div><br></div=
>
<div><br></div><div>--drop table table2;</div><div>create table table2(fiel=
d1 integer);</div><div>CREATE INDEX table2_field1_idx</div><div>=A0=A0ON ta=
ble2 (field1)</div><div>=A0=A0WHERE NOT field1 ISNULL;</div><div>insert int=
o table2 values(null);</div>
<div>insert into table2 select generate_series(1,100000);</div><div><br></d=
iv><div>vacuum analyze table2;</div><div><br></div><div>explain select * fr=
om table2 where field1 =3D 256988</div><div>explain select * from table2 wh=
ere field1 =3D 256988 and not field1 isnull</div>
</div><div><br></div><div><br></div>-- <br>&quot;Patriotism is the convicti=
on that your country is superior to all others because you were born in it.=
&quot; -- George Bernard Shaw<br>

--20cf307d01eef59a7704a155aa15--