X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 78AD38B9F8C for ; Tue, 8 Feb 2005 19:59:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 61411-08 for ; Tue, 8 Feb 2005 19:59:30 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4BEB88B9FE8 for ; Tue, 8 Feb 2005 19:59:30 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j18JxUm8009640; Tue, 8 Feb 2005 14:59:30 -0500 (EST) To: "Ben Young" Cc: pgsql-performance@postgresql.org Subject: Re: Slow Restoration of a template1 Database (ALTER GROUP) In-reply-to: <41A1CBC76FDECC42B67946519C6677A901D301FD@pippin.int.etrials.com> References: <41A1CBC76FDECC42B67946519C6677A901D301FD@pippin.int.etrials.com> Comments: In-reply-to "Ben Young" message dated "Tue, 08 Feb 2005 14:49:29 -0500" Date: Tue, 08 Feb 2005 14:59:29 -0500 Message-ID: <9639.1107892769@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/125 X-Sequence-Number: 10454 "Ben Young" 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