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


Thu, 25 Oct 2001 14:01:14 +0200
"Gabor Csuri" <gcsuri@auto999.com>
Index not used ! Why?
> 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.


Thu, 25 Oct 2001 14:49:38 +0200
"Gabor Csuri" <gcsuri@auto999.com>
Re: Index not used ! Why?
>  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...


Thu, 25 Oct 2001 15:31:50 +0200
"Nicolas Verger" <nicolas@verger.net>
Re: Index not used ! Why? + Little graphical client ...