Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 160579F9AB0 for ; Fri, 24 Aug 2007 11:22:48 -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 03322-07 for ; Fri, 24 Aug 2007 11:22:41 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id 1B9C69F9A00 for ; Fri, 24 Aug 2007 11:22:40 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id f5so715813nfh for ; Fri, 24 Aug 2007 07:22:39 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=MeGnB3J3YQ3aZ+pXu9KCOAsP5omfGswAod++lAmZveGpKjB0k3QWt37ioyDuEZ4Mh2U64H7B3QTbCmfLk/T/kJqIEj/bSoy2HKxpAT+TIH9/W2KrkZT3D6XTWRlqxFLNjcVVxUFsJiVHqLwaCa6uYplQZhdgGpHPx35jGqDPOzs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=tzwi/bdaZxqFz/DUYUq3Qqpv7Em9d72YV+xjhlZMnQva1gBlsBr01XYZly1WK26kl+CY7Re37UdIp/aDwgMju/IuBmlM0i8NnGGv3VWbapklmgZa8shVky6Ee4D5RActzMG9DFAwLCZ2JM4OLMy0rVYPfuX8/ALe0RsL+tKpV2c= Received: by 10.78.159.7 with SMTP id h7mr2029576hue.1187965358453; Fri, 24 Aug 2007 07:22:38 -0700 (PDT) Received: by 10.78.135.11 with HTTP; Fri, 24 Aug 2007 07:22:38 -0700 (PDT) Message-ID: <357fa7590708240722m204d678eid02b7ce3470eba1a@mail.gmail.com> Date: Fri, 24 Aug 2007 10:22:38 -0400 From: "Steven Flatt" To: "Mark Kirkwood" Subject: Re: When/if to Reindex Cc: "Tom Lane" , "Gregory Stark" , "Bill Moran" , "Vivek Khera" , "Pgsql performance" In-Reply-To: <46CE6883.10104@paradise.net.nz> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_70075_32380054.1187965358390" References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com> <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> <46CE6883.10104@paradise.net.nz> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/285 X-Sequence-Number: 26415 ------=_Part_70075_32380054.1187965358390 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/24/07, Mark Kirkwood wrote: > > 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. > > A suggestion that seems a bit like a leap backwards in time - maybe just > use the pg_class.relpages entry for the index size? Just throwing this out there (looking from a higher level)... Why do we even need to consider calling RelationGetNumberOfBlocks or looking at the pg_class.relpages entry? My understanding of the expected behaviour is that while a reindex is happening, all queries run against the parent table are planned as though the index isn't there (i.e. it's unusable). This may/will result in sub-optimal query plans, but the point is that reindex never blocks readers. Not sure if from an implementation standpoint it's easy to mark an index as "being reindexed" in which case the planner should just skip it. Steve ------=_Part_70075_32380054.1187965358390 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
On 8/24/07, Mark Kirkwood <markir@paradise.net.nz> wrote:
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.

A suggestion that seems a bit like a leap backwards in time - maybe just
use the pg_class.relpages entry for the index size?
 
Just throwing this out there (looking from a higher level)...
 
Why do we even need to consider calling RelationGetNumberOfBlocks or looking at the pg_class.relpages entry?  My understanding of the expected behaviour is that while a reindex is happening, all queries run against the parent table are planned as though the index isn't there ( i.e. it's unusable).  This may/will result in sub-optimal query plans, but the point is that reindex never blocks readers.  Not sure if from an implementation standpoint it's easy to mark an index as "being reindexed" in which case the planner should just skip it.
 
Steve
 
------=_Part_70075_32380054.1187965358390--