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

backend don't use index when querying by indexed column

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: backend don't use index when querying by indexed column
Date: 2001-08-06 23:27:57
Message-ID: 200108062327.f76NRvr49970@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Zdenek Habala (zhabala(at)telecom(dot)cz) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
backend don't use index when querying by indexed column

Long Description
I have a table named formula with this structure:

--------------------
 id_formula | integer                  | not null default nextval('"formula_id_formula_seq"'::text)
 id_loan    | integer                  |
 cisfor     | integer                  |
 castpr     | integer                  |
 castuh     | integer                  |
 rt_datfor  | integer                  |
 rt_datspl  | integer                  |
 rt_datvlo  | integer                  |
 kdo        | character(8)             |
 plod       | timestamp with time zone |
 pldo       | timestamp with time zone |
Indices: formula_id_loan,
         predpis_id_formula_key
 
Index "predpis_id_formula_key"
 Attribute  |  Type
------------+---------
 id_formula | integer
btree

when i try to explain this select i will got this:

explain select * from formula where id_formula = 1;
NOTICE:  QUERY PLAN:
Seq Scan on formula  (cost=0.00..10919.89 rows=4576 width=72)

but when i try to explain modified select like this:
explain select * from formula where id_formula = 1 and id_formula=id_formula;
NOTICE:  QUERY PLAN:
 Index Scan using predpis_id_formula_key on formula  (cost=0.00..11952.57 rows=46
 width=72)

backend uses the index.

Is it a bug or isn't it ? ( I have hundreds of other tables and any one of them have not this problem, only that formula one. )

Thanx for explanation.

Sample Code


No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-08-06 23:55:56
Subject: Re: backend don't use index when querying by indexed column
Previous:From: Rich MorinDate: 2001-08-06 22:14:44
Subject: assorted documentation errors

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