From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
Date: Tue, 14 Sep 2004 22:34:53 -0400
Message-ID: <60k6uw1cqa.fsf@dev6.int.libertyrms.info> (raw)
References: <20040914223333.GL56059@decibel.org>
<NOEFLCFHBPDAFHEIPGBOEEHPCEAA.simon@2ndquadrant.com>
simon@2ndquadrant.com ("Simon Riggs") writes:
> Well, its fairly straightforward to auto-generate the UNION ALL view, and
> important as well, since it needs to be re-specified each time a new
> partition is loaded or an old one is cleared down. The main point is that
> the constant placed in front of each table must in some way relate to the
> data, to make it useful in querying. If it is just a unique constant, chosen
> at random, it won't do much for partition elimination. So, that tends to
> make the creation of the UNION ALL view an application/data specific thing.
Ah, that's probably a good thought.
When we used big "UNION ALL" views, it was with logging tables, where
there wasn't really any meaningful distinction between partitions.
So you say that if the VIEW contains, within it, meaningful constraint
information, that can get applied to chop out irrelevant bits?
That suggests a way of resurrecting the idea...
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...
--
"cbbrowne","@","acm.org"
http://www3.sympatico.ca/cbbrowne/x.html
But what can you do with it? -- ubiquitous cry from Linux-user
partner. -- Andy Pearce, <ajp@hpopd.pwd.hp.com>
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: cbbrowne@acm.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
In-Reply-To: <60k6uw1cqa.fsf@dev6.int.libertyrms.info>
* 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