From: Stacy White <harsh@computer.org>
To: Josh Berkus <josh@agliodbs.com>
To: Greg Stark <gsstark@mit.edu>
Cc: pgsql-performance@postgresql.org
Subject: Re: Partitioned table performance
Date: Tue, 21 Dec 2004 17:41:39 -0800
Message-ID: <006901c4e7c7$62118500$0200a8c0@grownups> (raw)
References: <000a01c4da74$84e072e0$0200a8c0@grownups>
<200412151025.02262.josh@agliodbs.com>
<87oegvqrst.fsf@stark.xeocode.com>
<200412151156.40908.josh@agliodbs.com>
<87vfb3p40w.fsf@stark.xeocode.com>
The discussion seems to have diverged a little, so I don't feel too bad
about making some semi-off-topic comments.
From: "Greg Stark" <gsstark@mit.edu>
> Like I said though, we found "global indexes" defeated the whole purpose.
First semi-off-topic comment: I think this depends on the index, the data,
and the goal of the partitioning. We use partitioning on one of our Oracle
projects for performance rather than managability. In this case, a global
index on a non-partitioned field can be helpful.
Imagine an 'orders' table with 100 partitions on week. Products have a
short life cycle, and are typically around for only a week or two. A query
like 'SELECT * FROM orders WHERE product_no = ?' forces a lookup on 100
different local indexes, but only one global index.
Second-semi-off-topic comment: Josh mentioned that Oracle's partitioning
introduces it's own overhead, so I re-ran my earlier benchmarks on one of
our Oracle machines.
I believe Oracle's licensing agreement prohibits me from posting any
benchmarks, so all I'll say is that Postgres' inheritance partitioning
implementation is _very_ low overhead, and even union views are competitive.
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: harsh@computer.org, josh@agliodbs.com, gsstark@mit.edu
Subject: Re: Partitioned table performance
In-Reply-To: <006901c4e7c7$62118500$0200a8c0@grownups>
* 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