Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 869569FB638
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  9 Aug 2007 10:51:55 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 86815-04-2
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu,  9 Aug 2007 10:51:41 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.169])
	by postgresql.org (Postfix) with ESMTP id 328599FBF09
	for <pgsql-performance@postgresql.org>;
	Thu,  9 Aug 2007 10:51:48 -0300 (ADT)
Received: by ug-out-1314.google.com with SMTP id a2so387851ugf
	for <pgsql-performance@postgresql.org>;
	Thu, 09 Aug 2007 06:51:43 -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=gJ1gg3Z4lVohrtXrj0nu15Un+pE0Kg0MSySH21KlXrnMx/E2U/7yM6TtRcxE5PktjJ36UlNUo0IM3h7wQIrpxSoKX5SlhjbKnJeMDz5971gmg1B+KDcPhnR4smO4AQSqPjyL03jo5/ZCVgjoWARSigghOGrxBYVeffki1gAZ5Ig=
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=lEbF+ziEp1Km8m2bQNZ1QFWSBYXlnzG8BmgBVH9gX3Wu+ad+1bR9980MQhdGcRbAY+zKvOB6Pt5JTmCXpGC9PPlgMV7ZK11ua4YywAAIbLKij0SJRq5FYweuTOls9ZUNfivRJlz9P+VJMhyRR4SDVo8RUIIwKCvr/OJ4aQ/x/sI=
Received: by 10.78.183.8 with SMTP id g8mr892583huf.1186667502926;
	Thu, 09 Aug 2007 06:51:42 -0700 (PDT)
Received: by 10.78.134.7 with HTTP; Thu, 9 Aug 2007 06:51:42 -0700 (PDT)
Message-ID: <357fa7590708090651p71b9bc47n66739f8dbbb4ff57@mail.gmail.com>
Date: Thu, 9 Aug 2007 09:51:42 -0400
From: "Steven Flatt" <steven.flatt@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: When/if to Reindex
Cc: "Bill Moran" <wmoran@collaborativefusion.com>,
	"Vivek Khera" <vivek@khera.org>,
	"Pgsql performance" <pgsql-performance@postgresql.org>
In-Reply-To: <11801.1186624308@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_108489_16111203.1186667502893"
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>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/84
X-Sequence-Number: 26214

------=_Part_108489_16111203.1186667502893
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 8/8/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I'd make the same remark about Steven's case: if possible, don't create
> the indexes at all until you've loaded the table fully.


We considered this, however in some of our 12-hour partitions, there are
upwards of 50 or 60 million rows near the end of the 12 hours so read
performance gets bad on the current partition very quickly if there are no
indexes.

It makes more sense for us to have ~1 hour's worth of reindexing afterwards
during which read performance on that partition is "compromised".

Steve

------=_Part_108489_16111203.1186667502893
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div><span class="gmail_quote">On 8/8/07, <b class="gmail_sendername">Tom Lane</b> &lt;<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:</span>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">I&#39;d make the same remark about Steven&#39;s case: if possible, don&#39;t create<br>the indexes at all until you&#39;ve loaded the table fully.
</blockquote>
<div>&nbsp;</div>
<div>We considered this, however in some of our 12-hour partitions, there are upwards of 50 or 60 million rows&nbsp;near the end of the 12 hours so read performance&nbsp;gets bad&nbsp;on the current partition very quickly if there are no indexes.
</div>
<div>&nbsp;</div>
<div>It makes more sense for us to have ~1 hour&#39;s worth of reindexing afterwards during which read performance&nbsp;on that partition&nbsp;is &quot;compromised&quot;.</div>
<div>&nbsp;</div>
<div>Steve</div>
<div>&nbsp;</div></div>

------=_Part_108489_16111203.1186667502893--