Message-ID: <4CAA1E1B.8080505@agliodbs.com>
Date: Mon, 04 Oct 2010 11:34:03 -0700
From: Josh Berkus <josh@agliodbs.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: pgsql-performance@postgresql.org
Subject: Re: Issue for partitioning with extra check constriants
References: <4CA672D7.3070405@agliodbs.com> <1548.1285977377@sss.pgh.pa.us>
	<4CA67580.7070003@agliodbs.com> <6813.1285978581@sss.pgh.pa.us>
In-Reply-To: <6813.1285978581@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit


> And your point is?  The design center for the current setup is maybe 5
> or 10 partitions.  We didn't intend it to be used for more partitions
> than you might have spindles to spread the data across.

Where did that come from?  It certainly wasn't anywhere when the feature
was introduced.  Simon intended for this version of partitioning to
scale to 100-200 partitions (and it does, provided that you dump all
other table constraints), and partitioning has nothing to do with
spindles.  I think you're getting it mixed up with tablespaces.

The main reason for partitioning is ease of maintenance (VACUUM,
dropping partitions, etc.) not any kind of I/O optimization.

I'd like to add the following statement to our docs on partitioning, in
section 5.9.4:

=====

Constraint exclusion is tested for every CHECK constraint on the
partitions, even CHECK constraints which have nothing to do with the
partitioning scheme.  This can add siginficant extra planner time,
especially if your partitions have CHECK constraints which are costly to
evaluate.  For performance, it can be a good idea to eliminate all extra
CHECK constraints on partitions or to re-implement them as triggers.

=====

>In case you haven't noticed, we have very finite
> amounts of manpower that's competent to do planner surgery.

Point.


-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com