Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 8B4CE6502A5
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 27 Aug 2008 17:32:50 -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 77931-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 27 Aug 2008 17:32:42 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from lists.commandprompt.com (host-159.commandprompt.net
	[207.173.203.159])
	by postgresql.org (Postfix) with ESMTP id 6974564FCDA
	for <pgsql-performance@postgresql.org>;
	Wed, 27 Aug 2008 17:32:44 -0300 (ADT)
Received: from perhan.alvh.no-ip.org (190-95-19-36.bk17-dsl.surnet.cl
	[190.95.19.36]) (authenticated bits=0)
	by lists.commandprompt.com (8.13.8/8.13.8) with ESMTP id m7RKZfEP030389
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO);
	Wed, 27 Aug 2008 13:35:44 -0700
Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000)
	id C3ACE47CA7; Wed, 27 Aug 2008 16:32:34 -0400 (CLT)
Date: Wed, 27 Aug 2008 16:32:34 -0400
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Jerry Champlin <jchamplin@absolute-performance.com>
Cc: "'Tom Lane'" <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: Is there a way to SubPartition?
Message-ID: <20080827203234.GF4071@alvh.no-ip.org>
References: <48B55A8D.1050209@absolute-performance.com>
	<22840.1219845710@sss.pgh.pa.us>
	<006f01c90882$84f80ff0$8ee82fd0$@com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <006f01c90882$84f80ff0$8ee82fd0$@com>
User-Agent: Mutt/1.5.18 (2008-05-17)
X-Greylist: Sender succeeded SMTP AUTH, not delayed by milter-greylist-4.0
	(lists.commandprompt.com [207.173.203.159]);
	Wed, 27 Aug 2008 13:35:45 -0700 (PDT)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.41 tagged_above=0 required=5
	tests=SUBJECT_FUZZY_TION=0.41
X-Spam-Level: 
X-Archive-Number: 200808/354
X-Sequence-Number: 31235

Jerry Champlin wrote:
> 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.

Perhaps you do not realize this, but this is an exciting report to read.
Not many years ago, this kind of system would have been unthinkable.
We've now tuned the system so that people is starting to consider it,
and for a lot of people it is working.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support