From: | Kristofer Munn <kmunn(at)munn(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Index Puzzle for you |
Date: | 1999-12-29 02:54:50 |
Message-ID: | Pine.LNX.4.04.9912282140110.2149-100000@munn.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-12-29 05:49:12 | Re: [HACKERS] Index Puzzle for you |
Previous Message | Hiroshi Inoue | 1999-12-29 02:00:08 | RE: [HACKERS] can't link libpq.so(inet_aton() not found) |