From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ben Young <Ben.Young@etrials.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow Restoration of a template1 Database (ALTER GROUP)
Date: Tue, 08 Feb 2005 14:59:29 -0500
Message-ID: <9639.1107892769@sss.pgh.pa.us> (raw)
In-Reply-To: <41A1CBC76FDECC42B67946519C6677A901D301FD@pippin.int.etrials.com>
References: <41A1CBC76FDECC42B67946519C6677A901D301FD@pippin.int.etrials.com>
"Ben Young" <Ben.Young@etrials.com> writes:
> template1=# VACUUM FULL VERBOSE pg_group;
> INFO: --Relation pg_catalog.pg_group--
> INFO: Pages 124: Changed 1, reaped 124, Empty 0, New 0; Tup 4: Vac 966, Keep/VTL 0/0, UnUsed 156, MinLen 92, MaxLen 136; Re-using: Free/Avail. Space 1008360/1008360; EndEmpty/Avail. Pages 0/124.
> CPU 0.01s/0.00u sec elapsed 0.07 sec.
> INFO: Index pg_group_name_index: Pages 19072; Tuples 4: Deleted 966.
^^^^^
> CPU 1.51s/0.25u sec elapsed 17.19 sec.
> INFO: Index pg_group_sysid_index: Pages 4313; Tuples 4: Deleted 966.
^^^^
> CPU 0.48s/0.04u sec elapsed 6.06 sec.
Whoa. Can you say "index bloat"?
I think that the only way to fix this is to REINDEX pg_group, which IIRC
in 7.3 requires stopping the postmaster and doing it in a standalone
backend (check the REINDEX reference page for details). Make sure the
toast table gets reindexed too, as its index is oversized as well.
(Recent PG versions will automatically reindex the toast table when you
reindex its parent table, but I forget whether 7.3 did so; you might
have to explicitly "reindex pg_toast.pg_toast_1261".)
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, Ben.Young@etrials.com
Subject: Re: Slow Restoration of a template1 Database (ALTER GROUP)
In-Reply-To: <9639.1107892769@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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