Message-ID: <4147C560.9060702@joeconway.com>
Date: Tue, 14 Sep 2004 21:30:24 -0700
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.2) Gecko/20040803
MIME-Version: 1.0
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
References: <20040914223333.GL56059@decibel.org>
	<NOEFLCFHBPDAFHEIPGBOEEHPCEAA.simon@2ndquadrant.com>
	<60k6uw1cqa.fsf@dev6.int.libertyrms.info>
In-Reply-To: <60k6uw1cqa.fsf@dev6.int.libertyrms.info>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Chris Browne wrote:
> Might we set up the view as:
> 
> create view combination_of_logs as
>   select * from table_1 where txn_date between 'this' and 'that' 
>    union all
>   select * from table_2 where txn_date between 'this2' and 'that2' 
>    union all
>   select * from table_3 where txn_date between 'this3' and 'that3' 
>    union all
>   select * from table_4 where txn_date between 'this4' and 'that4' 
>    union all
>    ... ad infinitum
>    union all
>   select * from table_n where txn_date > 'start_of_partition_n';
> 
> and expect that to help, as long as the query that hooks up to this
> has date constraints?
> 
> We'd have to regenerate the view with new fixed constants each time we
> set up the tables, but that sounds like it could work...

That's exactly what we're doing, but using inherited tables instead of a 
union view. With inheritance, there is no need to rebuild the view each 
time a table is added or removed. Basically, in our application, tables 
are partitioned by either month or week, depending on the type of data 
involved, and queries are normally date qualified.

We're not completely done with our data conversion (from a commercial 
RDBMSi), but so far the results have been excellent. Similar to what 
others have said in this thread, the conversion involved restructuring 
the data to better suit Postgres, and the application (data 
analysis/mining vs. the source system which is operational). As a result 
we've compressed a > 1TB database down to ~0.4TB, and seen at least one 
typical query reduced from ~9 minutes down to ~40 seconds.

Joe