Обсуждение: Reindex vs Vacuum analyze

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

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

Vincent Janelle <random@goblinstudios.com> writes:
> when entries are deleted from an table they're not deleted from the
> index, and vacuum doesn't clean them up.

This is entirely false.

There is a problem with recovering space in an index if the range of
indexed values changes over time.  But claiming that we don't delete
index entries is just plain wrong.

            regards, tom lane