Re: Reindex vs Vacuum analyze

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(at)bigfoot(dot)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