Re: Partitioned table performance

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