pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Need Help with reindexing
2+ messages / 2 participants
[nested] [flat]

* Need Help with reindexing
@ 2005-03-14 06:11 Gourish Singbal <gourish@gmail.com>
  2005-03-14 07:39 ` Re: Need Help with reindexing Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Gourish Singbal @ 2005-03-14 06:11 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Need Help with reindexing
  2005-03-14 06:11 Need Help with reindexing Gourish Singbal <gourish@gmail.com>
@ 2005-03-14 07:39 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2005-03-14 07:39 UTC (permalink / raw)
  To: Gourish Singbal <gourish@gmail.com>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-03-14 07:39 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-14 06:11 Need Help with reindexing Gourish Singbal <gourish@gmail.com>
2005-03-14 07:39 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox