Re: Partitioned table performance

Josh Berkus <josh(at)agliodbs(dot)com> writes:

> > 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.

Like I said though, we found "global indexes" defeated the whole purpose. That
meant no global UNIQUE constraints for us when we went to partitioned tables.
It gave the DBAs the willies but it really wasn't a big deal.

You can still do unique local indexes on a specific partition. So as long as
your partition key is in the primary key you can have a trustworthy primary
key.

And even if not, you usually find you're only loading data into only one
partition. In most applications it's pretty hard to get a record from two
different partitions with conflicting IDs and not hard to check for. You could
easily put a constraint saying that all PO numbers in the new fiscal year have
to be greater than the last PO number from last year, for example.

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

The problem with global indexes is that adding or removing an entire partition
becomes a large job. [Actually with Postgres MVCC I suppose removing might
not. But cleaning up would eventually be a large job, and the point remains
for adding a partition.]

Ideally adding and removing a partition should be a O(1) operation. No data
modification at all, purely catalog changes.

> > 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.

I think the hard part lies in the optimizer actually. The semantics of the
operations to manipulate partitions might be tricky to get right but the
coding should be straightforward. Having the optimizer be able to recognize
when it can prune partitions will be a lot of work.

--
greg