Skip site navigation (1) Skip section navigation (2)

Index Puzzle for you

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 (view raw or flat)
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


Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 1999-12-29 05:49:12
Subject: Re: [HACKERS] Index Puzzle for you
Previous:From: Hiroshi InoueDate: 1999-12-29 02:00:08
Subject: RE: [HACKERS] can't link libpq.so(inet_aton() not found)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group