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 B3B2D52AA7 for ; Mon, 18 Apr 2005 17:14:04 -0300 (ADT) 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 68445-06 for ; Mon, 18 Apr 2005 20:13:55 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B8F06533C8 for ; Mon, 18 Apr 2005 17:13:54 -0300 (ADT) 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 j3IKDsTN025364; Mon, 18 Apr 2005 16:13:54 -0400 (EDT) To: josh@agliodbs.com Cc: Bill Chandler , pgsql-perform Subject: Re: Question on REINDEX In-reply-to: <200504181233.56784.josh@agliodbs.com> References: <20050418192142.84419.qmail@web51405.mail.yahoo.com> <200504181233.56784.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Mon, 18 Apr 2005 12:33:56 -0700" Date: Mon, 18 Apr 2005 16:13:54 -0400 Message-ID: <25363.1113855234@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/437 X-Sequence-Number: 11834 Josh Berkus writes: >> 1) When is it necessary to run REINDEX or drop/create >> an index? All I could really find in the docs is: > If you need to VACUUM FULL, you need to REINDEX as well. For example, if you > drop millions of rows from a table. That's probably a pretty good rule of thumb. It's worth noting that VACUUM FULL tends to actively bloat indexes, not reduce them in size, because it has to create new index entries for the rows it moves before it can delete the old ones. So if a VACUUM FULL moves many rows you are likely to see the indexes get bigger not smaller. > Better to up your max_fsm_pages and do regular VACUUMs regularly and > frequently so that you don't have to REINDEX at all. Yes, definitely. Also consider using CLUSTER rather than VACUUM FULL when you need to clean up after massive deletions from a table. It's not any less intrusive in terms of locking, but it's often faster and it avoids the index bloat problem (since it effectively does a REINDEX). regards, tom lane