Message-ID: <4148AC71.80908@joeconway.com>
Date: Wed, 15 Sep 2004 13:56:17 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.7.2) Gecko/20040803
MIME-Version: 1.0
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Chris Browne <cbbrowne@acm.org>, pgsql-performance@postgresql.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
References: <NOEFLCFHBPDAFHEIPGBOEEJACEAA.simon@2ndquadrant.com>
In-Reply-To: <NOEFLCFHBPDAFHEIPGBOEEJACEAA.simon@2ndquadrant.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Simon Riggs wrote:
> Joe,
> 
> Your application is very interesting. I've just read your OSCON paper. I'd
> like to talk more about that. Very similar to Kalido.
> 
> ...but back to partitioning momentarily: Does the performance gain come from
> partition elimination of the inherited tables under the root?

I think the major part of the peformance gain comes from the fact that 
the source database has different needs in terms of partitioning 
criteria because of it's different purpose. The data is basically 
partitioned by customer installation instead of by date. Our converted 
scheme partitions by date, which is in line with the analytical queries 
run at the corporate office. Again, this is an argument in favor of not 
simply porting what you're handed.

We might get similar query performance with a single large table and 
multiple partial indexes (e.g. one per month), but there would be one 
tradeoff and one disadvantage to that:
1) The indexes would need to be generated periodically -- this is a 
tradeoff since we currently need to create inherited tables at the same 
periodicity
2) It would be much more difficult to "roll off" a month's worth of data 
when needed. The general idea is that each month we create a new monthly 
table, then archive and drop the oldest monthly table. If all the data 
were in one big table we would have to delete many millions of rows from 
a (possibly) multibillion row table, and then vacuum that table -- no 
thanks ;-)

Joe