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

Поиск
Список
Период
Сортировка

Reindex vs Vacuum analyze

От
"Gaetano Mendola"
Дата:
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



Re: Reindex vs Vacuum analyze

От
Vincent Janelle
Дата:
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

Re: Reindex vs Vacuum analyze

От
Tom Lane
Дата:
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