Index not used ! Why?
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
Hello!
It needs some help by the command
VACUUM [VERBOSE] ANALYZE table;
to choose the ideal query strategy.
How can I choose better query strategy than ...WHERE key_field = x; ?
Regards, Gabor.
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=> 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
Try to create a unique index :
CREATE UNIQUE INDEX banners_b_no_key ON banners (b_no);
or specify a primary key :
ALTER TABLE banners ADD CONSTRAINT pk_banners PRIMARY KEY (b_no);
then ANALYZE your table ....
-- Nicolas --
We ( me and my teammate ) try to create a little graphical client for
PostgreSQL in Java. If someone want to try it :
http://pgInhaler.ifrance.com. It's an alpha version with lots of bugs... Try
it and send us your feedback to pginhaler@ifrance.com... Thanx...