Joe Conway <[EMAIL PROTECTED]> 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.
Advertising
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 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
