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
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
