From: Greg Stark <gsstark@mit.edu>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org, Greg Stark <gsstark@mit.edu>
Cc: Stacy White <harsh@computer.org>
Subject: Re: Partitioned table performance
Date: 15 Dec 2004 17:53:19 -0500
Message-ID: <87vfb3p40w.fsf@stark.xeocode.com> (raw)
In-Reply-To: <200412151156.40908.josh@agliodbs.com>
References: <000a01c4da74$84e072e0$0200a8c0@grownups>
<200412151025.02262.josh@agliodbs.com>
<87oegvqrst.fsf@stark.xeocode.com>
<200412151156.40908.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsstark@mit.edu, josh@agliodbs.com, harsh@computer.org
Subject: Re: Partitioned table performance
In-Reply-To: <87vfb3p40w.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox