Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id F05649F9A14 for ; Fri, 24 Aug 2007 02:27:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 44600-01 for ; Fri, 24 Aug 2007 02:27:21 -0300 (ADT) X-Greylist: delayed 00:15:04.691405 by SQLgrey-1.7.5 Received: from s-utl02-sjpop.stsn.net (s-utl02-sjpop.stsn.net [72.254.0.202]) by postgresql.org (Postfix) with SMTP id 8BE8C9F9A0F for ; Fri, 24 Aug 2007 02:27:21 -0300 (ADT) Received: from s-utl02-sjpop.stsn.net ([127.0.0.1]) by s-utl02-sjpop.stsn.net (SMSSMTP 4.1.2.20) with SMTP id M2007082322115329555 ; Thu, 23 Aug 2007 22:11:53 -0700 Received: from wormwood.local ([10.64.55.62]) by s-utl02-sjpop.stsn.net (using TLSv1/SSLv3 with cipher AES256-SHA (256 bits)); Thu, 23 Aug 2007 22:11:52 -0700 Message-ID: <46CE6883.10104@paradise.net.nz> Date: Thu, 23 Aug 2007 22:11:31 -0700 From: Mark Kirkwood User-Agent: Thunderbird 2.0.0.6 (Macintosh/20070728) MIME-Version: 1.0 To: Tom Lane CC: Steven Flatt , Gregory Stark , Bill Moran , Vivek Khera , Pgsql performance Subject: Re: When/if to Reindex References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com> <38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org> <357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com> <20070808152757.9eb9bbcc.wmoran@collaborativefusion.com> <11801.1186624308@sss.pgh.pa.us> <357fa7590708090651p71b9bc47n66739f8dbbb4ff57@mail.gmail.com> <357fa7590708220955h441cd75ap4c0f0342a1c30a14@mail.gmail.com> <873aybw2le.fsf@oxford.xeocode.com> <357fa7590708231525h2f48ce4dw3e48ecca5ccddf72@mail.gmail.com> <17370.1187927942@sss.pgh.pa.us> In-Reply-To: <17370.1187927942@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200708/279 X-Sequence-Number: 26409 Tom Lane wrote: > > The fly in the ointment is that after collecting the pg_index definition > of the index, plancat.c also wants to know how big it is --- it calls > RelationGetNumberOfBlocks. And that absolutely does look at the > physical storage, which means it absolutely is unsafe to do in parallel > with a REINDEX that will be dropping the old physical storage at some > point. > > So maybe we are stuck and we have to say "that doesn't work anymore". > But it feels like we might not be too far away from letting it still > work. Thoughts, ideas? > A suggestion that seems a bit like a leap backwards in time - maybe just use the pg_class.relpages entry for the index size? I'm punting that with autovacuum being enabled by default now, the relpages entries for all relations will be more representative than they used to in previous releases. Cheers Mark