Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 27B839F979C for ; Wed, 22 Aug 2007 13:55:36 -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 21807-09 for ; Wed, 22 Aug 2007 13:55:33 -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.186]) by postgresql.org (Postfix) with ESMTP id ED9429F977E for ; Wed, 22 Aug 2007 13:55:32 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id f5so205866nfh for ; Wed, 22 Aug 2007 09:55:32 -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=ntouEBAfFshIrckcpYkHquCN7QJealJKrztBNHnkj5HH+QlOSIkPUpZPdm5h517+dA5d0T6EXUHW8p04XLshU0IRtaOx+MWrUMXLRkEFriwNRfrCd/u+WuZDDgt1fIsnRLhq+dKY1RcxuNkNezzs51OsKKwzJdEryI63tk+CORo= 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=qUn4PQaRyjxn1VCSqW4uT8uMRpYXBV9UU/jEoK/LcPojO4VcZTiUfylqkccheaqOXRFxImH1SFbT6GZE959nMSu2O+WFxW4VoEvUFvzMRlbwMQpaEtVm6fXtsYT0TE8PKCAEObGmAlHEUn59B4RA7EM4to3G+6CvHzVZup3mvMM= Received: by 10.78.131.8 with SMTP id e8mr590008hud.1187801728646; Wed, 22 Aug 2007 09:55:28 -0700 (PDT) Received: by 10.78.134.7 with HTTP; Wed, 22 Aug 2007 09:55:28 -0700 (PDT) Message-ID: <357fa7590708220955h441cd75ap4c0f0342a1c30a14@mail.gmail.com> Date: Wed, 22 Aug 2007 12:55:28 -0400 From: "Steven Flatt" To: "Tom Lane" Subject: Re: When/if to Reindex Cc: "Bill Moran" , "Vivek Khera" , "Pgsql performance" In-Reply-To: <357fa7590708090651p71b9bc47n66739f8dbbb4ff57@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_227386_32672926.1187801728613" 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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/243 X-Sequence-Number: 26373 ------=_Part_227386_32672926.1187801728613 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline > > It makes more sense for us to have ~1 hour's worth of reindexing > afterwards during which read performance on that partition is "compromised". > So, based on the docs, I was expecting read performance to be compromised during a reindex, specifically reads would not be allowed to use the index: "REINDEX locks out writes but not reads of the index's parent table. It also takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index." However I'm seeing that all readers of that table are blocked until the reindex finishes, even reads that do not attempt to use the index. Is this a problem with the docs or a bug? I'm considering creating a new index with the same definition as the first (different name), so while that index is being created, read access to the table, and the original index, is not blocked. When the new index is created, drop the original index and rename the new index to the original, and we've essentially accomplished the same thing. In fact, why isn't reindex doing this sort of thing in the background anways? Thanks, Steve ------=_Part_227386_32672926.1187801728613 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
It makes more sense for us to have ~1 hour's worth of reindexing afterwards during which read performance on that partition is "compromised".
 
So, based on the docs, I was expecting read performance to be compromised during a reindex, specifically reads would not be allowed to use the index:
 
"REINDEX locks out writes but not reads of the index's parent table. It also takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index."

However I'm seeing that all readers of that table are blocked until the reindex finishes, even reads that do not attempt to use the index.  Is this a problem with the docs or a bug?
 
I'm considering creating a new index with the same definition as the first (different name), so while that index is being created, read access to the table, and the original index, is not blocked.  When the new index is created, drop the original index and rename the new index to the original, and we've essentially accomplished the same thing.  In fact, why isn't reindex doing this sort of thing in the background anways?
 
Thanks,
Steve
 
------=_Part_227386_32672926.1187801728613--