From: Joe Conway <mail@joeconway.com>
To: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
Date: Tue, 14 Sep 2004 21:30:24 -0700
Message-ID: <4147C560.9060702@joeconway.com> (raw)
In-Reply-To: <60k6uw1cqa.fsf@dev6.int.libertyrms.info>
References: <20040914223333.GL56059@decibel.org>
<NOEFLCFHBPDAFHEIPGBOEEHPCEAA.simon@2ndquadrant.com>
<60k6uw1cqa.fsf@dev6.int.libertyrms.info>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mail@joeconway.com, cbbrowne@acm.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres --
In-Reply-To: <4147C560.9060702@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox