"Vacuum analyze" VS "recreate index"

From: "Gaetano Mendola" <mendola(at)bigfoot(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: "Vacuum analyze" VS "recreate index"
Date: 2002-10-28 09:20:45
Message-ID: apivhb$29ac$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi all,
today I had a strange beaviour on a table with a partial index:

push=# select count(*) from jobs;
count
--------
426197
(1 row)

push=# select count(*) from jobs where status = 'r';
count
-------
6
(1 row)

the partial index is defined like this:
create index idx_jobs_status_r on jobs (status ) where status in
('r','0','a');

when I did this ( one month ago ) the query was running fine like:

push=# explain analyze select * from jobs where status = 'r';
NOTICE: QUERY PLAN:

Index Scan using idx_jobs_status_r on jobs (cost=0.00..2.11 rows=1
width=49) (actual time=0.07..0.80 rows=5 loops=1)
Total runtime: 0.90 msec

Today (before to drop the index and recreate it ) I had for the same query
a
Total runtime of ~ 10 secs.

I tried to do vacuum analyze but the total time remained the same.
Now I'm wandering about if I should do drop that index and
recreate it instead of do a vacuum analyze during the night.

Ciao
Gaetano.

Browse pgsql-admin by date

  From Date Subject
Next Message Bhuvan A 2002-10-28 12:06:47 pg_log: no such file or directory
Previous Message Brian McCane 2002-10-27 17:38:34 Missing pg_clog file