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.

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

Reply via email to