From: Chris Browne <cbbrowne@acm.org>
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
Date: Tue, 14 Sep 2004 22:34:53 -0400
Organization: cbbrowne Computing Inc
Lines: 44
Message-ID: <60k6uw1cqa.fsf@dev6.int.libertyrms.info>
References: <20040914223333.GL56059@decibel.org>
	<NOEFLCFHBPDAFHEIPGBOEEHPCEAA.simon@2ndquadrant.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.110003 (No Gnus v0.3) XEmacs/21.4 (Security Through
	Obscurity, linux)
Cancel-Lock: sha1:xNOHt2irSyfawKIfv8BLb47jKKU=
To: pgsql-performance@postgresql.org

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>