when entries are deleted from an table they're not deleted from the
index, and vacuum doesn't clean them up. reindex recreates the index.
It is suggested that you run a script at whatever necessary intervals to
recreate the indexes on your tables if they have large amounts of data
deleted from them on a regular basis.
Mine creates a temporary index, drops the old index, and renames the
temp index to the old one's name. After all that, then it performs a
vacuum.
On Fri, 1 Nov 2002 12:27:48 +0100
"Gaetano Mendola" <mendola@bigfoot.com> wrote:
> I repeat my simple experience for
> know about what is going on:
>
> push=# explain analyze select * from jobs where status = 'r';
> NOTICE: QUERY PLAN:
>
> Index Scan using idx_jobs_status_r on jobs (cost=0.00..8.57 rows=3770
> width=49)
> (actual time=19.26..1295.73 rows=5 loops=1)
> Total runtime: 1295.85 msec
>
> EXPLAIN
> push=# vacuum analyze jobs;
> VACUUM
> push=# explain analyze select * from jobs where status = 'r';
> NOTICE: QUERY PLAN:
>
> Index Scan using idx_jobs_status_r on jobs (cost=0.00..3.12 rows=1
> width=49)
> (actual time=0.08..1318.36 rows=5 loops=1)
> Total runtime: 1318.48 msec
>
> EXPLAIN
> push=# reindex table jobs;
> REINDEX
> push=# explain analyze select * from jobs where status = 'r';
> NOTICE: QUERY PLAN:
>
> Index Scan using idx_jobs_status_r on jobs (cost=0.00..3.12 rows=1
> width=49) (actual time=0.04..0.15 rows=4 loops=1)
> Total runtime: 0.24 msec
