Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 24B7A9FA2B2 for ; Fri, 2 Feb 2007 12:43:01 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 40609-07 for ; Fri, 2 Feb 2007 12:42:51 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id AA45C9FB289 for ; Fri, 2 Feb 2007 12:42:57 -0400 (AST) Received: from [192.168.42.189] (natpool.bovine.net [67.100.216.14]) (using TLSv1 with cipher AES128-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id 06AC056490; Fri, 2 Feb 2007 10:42:57 -0600 (CST) In-Reply-To: <200701310318.l0V3IWU08970@momjian.us> References: <200701310318.l0V3IWU08970@momjian.us> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <93FBF786-18A2-4BCC-AB5C-118B5451663F@decibel.org> Cc: Csaba Nagy , Jeremy Haile , Tom Lane , "Ed L." , Postgres general mailing list , Bill Moran , Alvaro Herrera Content-Transfer-Encoding: 7bit From: Jim Nasby Subject: Re: Index bloat of 4x Date: Thu, 1 Feb 2007 22:38:07 -0500 To: Bruce Momjian X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200702/133 X-Sequence-Number: 108623 Is there no way to change the index code to allow for moving index tuples from one page to another? If we could do that then presumably we could free up substantially more pages. On Jan 30, 2007, at 10:18 PM, Bruce Momjian wrote: > > Added to TODO: > > * Add REINDEX CONCURRENTLY, like CREATE INDEX CONCURRENTLY > > This is difficult because you must upgrade to an exclusive table > lock > to replace the existing index file. CREATE INDEX CONCURRENTLY > does not > have this complication. This would allow index compaction without > downtime. > > I understand the problems, but the need for this seems pretty clear. > > ---------------------------------------------------------------------- > ----- > > Csaba Nagy wrote: >> On Fri, 2007-01-19 at 20:03, Jeremy Haile wrote: >>> Is it feasible to add a "reindex concurrently" that doesn't lock the >>> table for the rebuild, then locks the table when doing a second >>> pass to >>> pickup rows that were changed after the first pass? Or something >>> like >>> that.... >> >> IIRC, the objection was the deadlock potential of any lock >> upgrade, and >> the problems of impossible cleanup on failure if something changed >> the >> permissions of the executing user in the meantime. That's why I >> think it >> would make sense if it could be done by a privileged background >> thread >> like the autovacuum ones, so the lock upgrade can be tried without >> blocking, as it can take quite some time till it succeeds, and the >> cleanup is possible due to the privileged nature of the executor. >> >> If there would be such a facility it would also need some policies to >> control time windows and priorities just as for autovacuum, that's >> why I >> connect it in my usage-focused mind to autovacuum. >> >> Cheers, >> Csaba. >> >> >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match > > -- > Bruce Momjian bruce@momjian.us > EnterpriseDB http://www.enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)