Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 001419FB25F
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon,  8 Jan 2007 16:02:34 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 89391-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon,  8 Jan 2007 16:02:23 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.232])
	by postgresql.org (Postfix) with ESMTP id 5A7149FB210
	for <pgsql-performance@postgresql.org>;
	Mon,  8 Jan 2007 16:02:25 -0400 (AST)
Received: by wx-out-0506.google.com with SMTP id i28so7833963wxd
	for <pgsql-performance@postgresql.org>;
	Mon, 08 Jan 2007 12:02:24 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=j34F3cGFbHLsEVATU91hVVpvtpjX+73HWph7r+qV79Fp6vvGh1+DH/93xz9D+1kfjHH+zvVp0KhcFG2QrMqhEtwcx/6zQqqrgAGXJdciVGDT/SRH33h8Ds+2bc9GjBbX8oQZQOJtVKV4iJAj/AEO+2bNcBoBiO9Ujv0cpbM947I=
Received: by 10.70.19.20 with SMTP id 20mr49306360wxs.1168286544685;
	Mon, 08 Jan 2007 12:02:24 -0800 (PST)
Received: by 10.70.102.8 with HTTP; Mon, 8 Jan 2007 12:02:24 -0800 (PST)
Message-ID: <357fa7590701081202p726c049ej61390dd1816d1369@mail.gmail.com>
Date: Mon, 8 Jan 2007 15:02:24 -0500
From: "Steven Flatt" <steven.flatt@gmail.com>
To: "Colin Taylor" <colin.taylor@gmail.com>
Subject: Re: table partioning performance
Cc: pgsql-performance@postgresql.org
In-Reply-To: <84d3067d0701062037k3229c9ddjbde73b1fd79ee209@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_71316_3066957.1168286544617"
References: <84d3067d0701062037k3229c9ddjbde73b1fd79ee209@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/100
X-Sequence-Number: 22701

------=_Part_71316_3066957.1168286544617
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 1/6/07, Colin Taylor <colin.taylor@gmail.com> wrote:

> Hi there,  we've partioned a table (using 8.2) by day due to the 50TB of
> data (500k row size, 100G rows) we expect to store it in a year.
> Our performance on inserts and selects against the master table is
> disappointing, 10x slower (with ony 1 partition constraint) than we get  by
> going to the partioned table directly.


Are you implementing table partitioning as described at:
http://developer.postgresql.org/pgdocs/postgres/ddl-partitioning.html ?

If yes, and if I understand your partitioning "by day" correctly, then you
have one base/master table with 366 partitions (inherited/child tables).  Do
each of these partitions have check constraints and does your master table
use rules to redirect inserts to the appropriate partition?  I guess I don't
understand your "only 1 partition constraint" comment.

We use partitioned tables extensively and we have observed linear
performance degradation on inserts as the number of rules on the master
table grows (i.e. number of rules = number of partitions).  We had to come
up with a solution that didn't have a rule per partition on the master
table.  Just wondering if you are observing the same thing.

Selects shouldn't be affected in the same way, theoretically, if you have
constraint_exclusion enabled.

Steve

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

<div><span class="gmail_quote">On 1/6/07, <b class="gmail_sendername">Colin Taylor</b> &lt;<a href="mailto:colin.taylor@gmail.com">colin.taylor@gmail.com</a>&gt; wrote:</span></div>
<blockquote class="gmail_quote" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: #ccc 1px solid">Hi there,&nbsp; we&#39;ve partioned a table (using 8.2) by day due to the 50TB of data (500k row size, 100G rows) we expect to store it in a year. 
<br>Our performance on inserts and selects against the master table is disappointing, 10x slower (with ony 1 partition constraint) than we get&nbsp; by going to the partioned table directly.</blockquote>
<div>&nbsp;</div>
<div>Are you implementing table partitioning as described at: <a href="http://developer.postgresql.org/pgdocs/postgres/ddl-partitioning.html">http://developer.postgresql.org/pgdocs/postgres/ddl-partitioning.html</a>&nbsp;?</div>

<div>&nbsp;</div>
<div>If yes, and if I understand your partitioning &quot;by day&quot; correctly, then you have one base/master table with 366 partitions (inherited/child tables).&nbsp; Do each of these partitions have check constraints and does your master table use rules to redirect inserts to the appropriate partition?&nbsp; I guess I don&#39;t understand your &quot;only 1 partition constraint&quot; comment.
</div>
<div>&nbsp;</div>
<div>We use partitioned tables extensively and we have observed linear performance degradation on inserts as the number of rules on the master table grows (i.e. number of rules = number of partitions).&nbsp; We had&nbsp;to come up with a solution that didn&#39;t have a&nbsp;rule per partition on the master table.&nbsp; Just wondering if you are observing the same thing.
</div>
<div>&nbsp;</div>
<div>Selects shouldn&#39;t be affected in the same way, theoretically, if you have constraint_exclusion enabled.</div>
<div>&nbsp;</div>
<div>Steve</div>
<div>&nbsp;</div>

------=_Part_71316_3066957.1168286544617--