Index not used ! Why?

From: "Gabor Csuri" <gcsuri(at)auto999(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Index not used ! Why?
Date: 2001-10-25 12:01:14
Message-ID: 012601c15d4c$bf8b9c60$0a01a8c0@csucsupc
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi All,

my database server has very high load in this morning.
I've found the problem. One of my index was not used so far!
it's interesting:

addb=> \d banners
Table "banners"
Attribute | Type | Modifier
------------+--------------------------+----------------------------------------------------
b_no | integer | not null default nextval('banners_b_no_seq'::text)
usr_no | integer |
b_ext | character varying(10) |
b_link | character varying(100) |
b_from | date |
b_to | date |
b_lastview | timestamp with time zone | default now()
b_maxview | integer |
b_curview | integer | default 0
b_maxclick | integer |
b_curclick | integer | default 0
b_weight | integer | default 1
b_curwg | double precision | default 0
b_active | boolean | default 'f'::bool
last_upd | timestamp with time zone | default now()
upd_usr | integer |
b_name | character varying(40) |
Indices: b_usr_no_idx,
banners_b_no_key

addb=> EXPLAIN SELECT b_link FROM banners WHERE b_no = 3;
NOTICE: QUERY PLAN:

Seq Scan on banners (cost=0.00..1.57 rows=1 width=12)

EXPLAIN
addb=> DROP INDEX banners_b_no_key;
DROP
addb=> CREATE INDEX banners_b_no_key ON banners (b_no);
CREATE
addb=> EXPLAIN SELECT b_link FROM banners WHERE b_no = 3;
NOTICE: QUERY PLAN:

Index Scan using banners_b_no_key on banners (cost=0.00..4.43 rows=1 width=12)

EXPLAIN
addb=>

Why index wasn't used ?
postgresql-7.1.2, redhat 7.0, kernel:2.2.19

Thanks, Gabor

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2001-10-25 12:46:49 Re: pgindent run
Previous Message Zeugswetter Andreas SB SD 2001-10-25 10:04:33 Re: Index of a table is not used (in any case)