From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: pgsql-performance@postgresql.org
Subject: Re: Partitioned table performance
Date: Wed, 15 Dec 2004 11:56:40 -0800
User-Agent: KMail/1.6.2
Cc: Greg Stark <gsstark@mit.edu>, "Stacy White" <harsh@computer.org>
References: <000a01c4da74$84e072e0$0200a8c0@grownups>
	<200412151025.02262.josh@agliodbs.com>
	<87oegvqrst.fsf@stark.xeocode.com>
In-Reply-To: <87oegvqrst.fsf@stark.xeocode.com>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200412151156.40908.josh@agliodbs.com>

Greg,

> Well Oracle has lots of partitioning intelligence pushed up to the planner
> to avoid overhead.
>
> If you have a query with something like "WHERE date = '2004-01-01'" and
> date is your partition key (even if it's a range) then Oracle will figure
> out which partition it will need at planning time.

Hmmm ... well, we're looking at making a spec for Postgres Table Partitioning.   
Maybe you could help?

> But I'm a bit puzzled. Why would Append have any significant cost? It's
> just taking the tuples from one plan node and returning them until they run
> out, then taking the tuples from another plan node. It should have no i/o
> cost and hardly any cpu cost. Where is the time going?

Beats me.  Tom?

> In my experience "global indexes" defeat the whole purpose of having the
> partitions. They make dropping and adding partitions expensive which was
> always the reason we wanted to partition something anyways.

Hmmm.    Possibly, I was just thinking about the cost to partitioned tables 
when you do a selection *not* on the partitioned axis.   Also that currently 
we can't enforce UNIQUE constraints across partitions.

But maybe reducing the cost of Append is the answer to this.

> It is handy having a higher level interface to deal with partitioned
> tables. You can create a single "local" or "segmented" index and not have
> to manually deal with all the partitions as separate tables. But that's
> just syntactic sugar.

Right, and the easy part.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco