Re: Re: Data Warehouse Reevaluation - MySQL vs Postgres --

Joe Conway <mail@joeconway.com> wrote on 15.09.2004, 06:30:24:
> 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.

Sounds interesting.

The performance gain comes from partition elimination of the inherited
tables under the root?

I take it the compression comes from use of arrays, avoiding the need
for additional rows and key overhead?

Best Regards, Simon Riggs