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.
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 |