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