Обсуждение: Need Help with reindexing

Guys,

I need some help to decide when i should reindex the indexes in the table.
Postgresql 7.4.5 and linux O.S.
Below is the results of vacuum analyze verbose on the table

INFO:  vacuuming "public.rpt_metrics"
INFO:  index "rpt_metrics_date_network_advertiser" now contains
102960776 row versions in 717963 pages
DETAIL:  198809 index pages have been deleted, 20000 are currently reusable.
CPU 30.95s/3.51u sec elapsed 770.69 sec.
INFO:  index "rpt_metrics_date_network" now contains 102960776 row
versions in 517246 pages
DETAIL:  179291 index pages have been deleted, 20000 are currently reusable.
CPU 21.11s/2.69u sec elapsed 917.34 sec.
INFO:  index "rpt_metrics_date" now contains 102960776 row versions in
516546 pages
DETAIL:  193753 index pages have been deleted, 20000 are currently reusable.
CPU 20.31s/2.70u sec elapsed 975.39 sec.
INFO:  "rpt_metrics": found 0 removable, 102960776 nonremovable row
versions in 2920718 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 56377053 unused item pointers.
0 pages are entirely empty.
CPU 123.67s/17.41u sec elapsed 3132.89 sec.
INFO:  analyzing "public.rpt_metrics"
INFO:  "rpt_metrics": 2920718 pages, 3000 rows sampled, 176302
estimated total rows
VACUUM

When should i ideally reindex this table ?.
(once in a month, weekly....etc)
--
Best,
Gourish Singbal

Gourish Singbal <gourish@gmail.com> writes:
> I need some help to decide when i should reindex the indexes in the table.
> ...
> When should i ideally reindex this table ?.
> (once in a month, weekly....etc)

It seems to need reindexing now, but that tells us nothing about how
often you might need to do it.

My advice is that you probably need to vacuum more often, rather than
deciding you need periodic reindexes.

            regards, tom lane