Index Puzzle for you

Hi all. Once again I come to you with a puzzle...

I have the following structures (related to this question) in my
[PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]
database according to a pg_dump of the schema (reformatted for
readability):

---------------------------------------------------------------------------

CREATE TABLE "tblissuearticle" (
"ixissue" int4 NOT NULL,
"ixarticle" int4 NOT NULL,
"ixprocessor" int4,
"ixmember" int4,
"iorder" int4,
"sstatus" character);

REVOKE ALL on "tblissuearticle" from PUBLIC;

CREATE INDEX "tblissuearticle_oid" on "tblissuearticle"
using btree ("oid" "oid_ops" );

CREATE INDEX "tblissuearticle_idx1" on "tblissuearticle"
using btree (
"ixissue" "int4_ops",
"ixarticle" "int4_ops",
"iorder" "int4_ops"
);

CREATE INDEX "tblissuearticle_idx2" on "tblissuearticle"
using btree ("ixissue" "int4_ops" );

---------------------------------------------------------------------------

Now I enter trusty psql to run some SQL statements. Notice the SECOND
EXPLAIN which I run. I aded the _idx2 index above after this statement
didn't catch _idx1 (partial index). Neither parts matched. I tried
dropping _idx1 and it still didn't use _idx2.

---------------------------------------------------------------------------

mail=> vacuum tblissuearticle ;
VACUUM
mail=> vacuum analyze tblissuearticle ;
VACUUM
mail=> explain select 1 from tblissuearticle where ixissue = 7
and ixarticle = 9;
NOTICE: QUERY PLAN:

Index Scan using tblissuearticle_idx1 on tblissuearticle
(cost=228.04 rows=1 width=0)

EXPLAIN
mail=> explain select 1 from tblissuearticle where ixissue = 7;
NOTICE: QUERY PLAN:

Seq Scan on tblissuearticle (cost=4076.63 rows=76338 width=0)

EXPLAIN
mail=> explain verbose select 1 from tblissuearticle where ixissue = 7;
NOTICE: QUERY DUMP:

{ SEQSCAN :cost 4076.63 :size 76338 :width 0 :state <> :qptargetlist ({
TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1 :resname
"?column?" :reskey 0 :reskeyop 0 :resgroupref 0 :resjunk false } :expr {
CONST :consttype 23 :constlen 4 :constisnull false :constvalue 4 [ 1 0 0 0
] :constbyval true }}) :qpqual ({ EXPR :typeOid 0 :opType op :oper { OPER
:opno 96 :opid 65 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1
:vartype 23 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype 23 :constlen 4 :constisnullfalse :constvalue 4 [ 7 0 0 0 ]
:constbyval true })}) :lefttree <> :righttree <> :extprm () :locprm ()
:initplan <> :nprm 0 :scanrelid 1 }

NOTICE: QUERY PLAN:

Seq Scan on tblissuearticle (cost=4076.63 rows=76338 width=0)

EXPLAIN

---------------------------------------------------------------------------

Hoping someone can shed some light on this for me. Happy Holidays...

- K

Kristofer Munn * KMI * 973-509-9414 * AIM KrMunn * ICQ 352499 * www.munn.com