Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 97B2964FCF3
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 27 Aug 2008 17:21:40 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 64019-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 27 Aug 2008 17:21:34 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from fort-mx02.den.api.local (unknown [66.35.32.228])
	by postgresql.org (Postfix) with ESMTP id 57C0064FCDA
	for <pgsql-performance@postgresql.org>;
	Wed, 27 Aug 2008 17:21:37 -0300 (ADT)
Received: from jgcd430 ([192.168.10.138])
	by fort-mx02.den.api.local (8.13.1/8.13.1) with ESMTP id m7RKLY9J003936;
	Wed, 27 Aug 2008 14:21:34 -0600
From: "Jerry Champlin" <jchamplin@absolute-performance.com>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>
References: <48B55A8D.1050209@absolute-performance.com>
	<22840.1219845710@sss.pgh.pa.us>
In-Reply-To: <22840.1219845710@sss.pgh.pa.us>
Subject: Re: Is there a way to SubPartition? 
Date: Wed, 27 Aug 2008 14:21:41 -0600
Message-ID: <006f01c90882$84f80ff0$8ee82fd0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AckITXSwkPh8aI1rQFydiNiEv/TpvAAL3c/g
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.51 tagged_above=0 required=5 tests=RDNS_NONE=0.1,
	SUBJECT_FUZZY_TION=0.41
X-Spam-Level: 
X-Archive-Number: 200808/353
X-Sequence-Number: 31234

If it were implemented in such a way that when the top level pruning
happens, a set of 3 sub partitions is selected from say 18 total and then at
the next level is selects the 3 matching sub partitions from each matched
group of 30 then you are only looking at 18+3*30 = 108 instead of 548 checks
to evaluate <example assumes monthly first level partitioning and daily sub
partitioning>.  If this is not supported, then we will need to solve the
problem a different way - probably weekly partitions and refactor the code
to decrease updates by at least an order of magnitude.  While we are in the
process of doing this, is there a way to make updates faster?  Postgresql is
spending a lot of CPU cycles for each HOT update.  We have
synchronous_commit turned off, commit siblings set to 5, commit_delay set to
50,000.  With synchronous_commit off does it make any sense to be grouping
commits?  Buffers written by the bgwriter vs checkpoint is 6 to 1.  Buffers
written by clients vs buffers by checkpoint is 1 to 6.  Is there anything
obvious here?

-Jerry


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Wednesday, August 27, 2008 8:02 AM
To: Jerry Champlin
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Is there a way to SubPartition? 

Jerry Champlin <jchamplin@absolute-performance.com> writes:
> Is there a way to use multi-level inheritance to achieve sub 
> partitioning that the query optimizer will recognize?

No, I don't think so.  How would that make things any better anyway?
You're still going to end up with the same very large number of
partitions.

			regards, tom lane