X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B319F528DF for ; Thu, 28 Jul 2005 02:09:03 -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 42491-02 for ; Thu, 28 Jul 2005 05:08:57 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 53E115284A for ; Thu, 28 Jul 2005 02:08:56 -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 j6S58t4p020368; Thu, 28 Jul 2005 01:08:55 -0400 (EDT) To: Chris Browne Cc: pgsql-sql@postgresql.org Subject: Re: REINDEX DATABASE In-reply-to: <608xzs1d1n.fsf@dba2.int.libertyrms.com> References: <60pst51ocd.fsf@dba2.int.libertyrms.com> <608xzs1d1n.fsf@dba2.int.libertyrms.com> Comments: In-reply-to Chris Browne message dated "Wed, 27 Jul 2005 11:31:00 -0400" Date: Thu, 28 Jul 2005 01:08:55 -0400 Message-ID: <20367.1122527335@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: 200507/237 X-Sequence-Number: 22215 Chris Browne writes: > achill@matrix.gatewaynet.com (Achilleus Mantzios) writes: >> I am just saying that the common saying "reindex is not needed for >> 7.4+" maybe is not true in all circumstances. > ... > In versions earlier than 7.4, running a REINDEX periodically was > *essential* if you had update patterns consistent with the (remarkably > common) scenario described above. > This reason to reindex (which was the main reason we required > reindexing when using 7.2) has been resolved and gone away in 7.4. > There may be other factors that could mandate REINDEX; as far as I can > tell, the main such factor that remains would be where a table sees > enormous numbers of updates but is not VACUUMed often enough. I think the case that 7.4 resolved is where you have a continually-moving window of index values; for example, an index on a timestamp column in a table where you delete entries older than 30 days. Before 7.4, index pages for timestamps older than 30 days would become empty and then just sit there, with no other way to reclaim them than REINDEX. The case that isn't resolved yet is where you have a usage pattern that causes a lot of index pages to become mostly but not entirely empty. For example, your entries are timestamps, and you have a cleanup process that removes just 99 out of every 100 successive entries. This'll leave you with just a couple of index entries per page, which might not be infinite bloat but it's surely not too efficient. regards, tom lane