Message-ID: <006901c4e7c7$62118500$0200a8c0@grownups>
From: "Stacy White" <harsh@computer.org>
To: "Josh Berkus" <josh@agliodbs.com>, "Greg Stark" <gsstark@mit.edu>
Cc: <pgsql-performance@postgresql.org>
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>
Subject: Re: Partitioned table performance
Date: Tue, 21 Dec 2004 17:41:39 -0800
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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.