X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id DB4FFD1C90F for ; Mon, 28 Jul 2003 21:29:05 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 21458-04 for ; Mon, 28 Jul 2003 18:28:10 -0300 (ADT) Received: from web21102.mail.yahoo.com (web21102.mail.yahoo.com [216.136.227.104]) by svr1.postgresql.org (Postfix) with SMTP id 5C0F0D1C508 for ; Mon, 28 Jul 2003 18:29:03 -0300 (ADT) Message-ID: <20030728212905.79549.qmail@web21102.mail.yahoo.com> Received: from [65.206.239.222] by web21102.mail.yahoo.com via HTTP; Mon, 28 Jul 2003 14:29:05 PDT Date: Mon, 28 Jul 2003 14:29:05 -0700 (PDT) From: Shankar K Subject: Rebuild indexes To: pgsql-performance@postgresql.org Cc: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/278 X-Sequence-Number: 9618 Hi Everyone, I've a kind of less inserts/mostly updates table, which we vacuum every half-hour. here is the output of vacuum analyze INFO: --Relation public.accounts-- INFO: Index accounts_u1: Pages 1498; Tuples 515: Deleted 179. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: Index accounts_u2: Pages 2227; Tuples 515: Deleted 179. CPU 0.00s/0.00u sec elapsed 0.01 sec. INFO: Index accounts_u3: Pages 246; Tuples 515: Deleted 179. CPU 0.00s/0.00u sec elapsed 0.00 sec. however its indexes keeps growing on and on. After surfing the manuals for a while, i came to know that vacuum doesn't clears up dead tuples caused by updates. so i then decided to do reindex online, but that makes exclusive lock on table which would prevent writing on to tables. finally i'm at a point where i decided to do index swapping. for e.g. 1. create index accounts_u1_swap,accounts_u2_swap and accounts_u3_swap in addition to the original indexes 2. analyze table to update stats, so that the table knows about new indexes. 3. drop original indexes 4. i wish i had a rename index command to rename _swap to its original index name. now create indexes with original name 5. follow #2 and #3 (now drop _swap indexes) Is there a better way to do this. comments are appreciated. thanks -Shankar __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com