Re: Data Warehouse Reevaluation - MySQL vs Postgres --

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