From: Jim C. Nasby <decibel@decibel.org>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org, Mark Cotner <mcotner@yahoo.com>
Cc: Christopher Browne <cbbrowne@acm.org>
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
Date: Tue, 14 Sep 2004 17:33:33 -0500
Message-ID: <20040914223333.GL56059@decibel.org> (raw)
In-Reply-To: <NOEFLCFHBPDAFHEIPGBOCEFPCEAA.simon@2ndquadrant.com>
References: <m3acvulx5f.fsf@wolfe.cbbrowne.com>
<NOEFLCFHBPDAFHEIPGBOCEFPCEAA.simon@2ndquadrant.com>
On Mon, Sep 13, 2004 at 11:07:35PM +0100, Simon Riggs wrote:
> PostgreSQL's functionality is in many ways similar to Oracle Partitioning.
>
> Loading up your data in many similar tables, then creating a view like:
>
> CREATE VIEW BIGTABLE (idate, col1, col2, col3...) AS
> SELECT 200409130800, col1, col2, col3... FROM table200409130800
> UNION ALL
> SELECT 200409131000, col1, col2, col3... FROM table200409131000
> UNION ALL
> SELECT 200409131200, col1, col2, col3... FROM table200409131200
> ...etc...
>
> will allow the PostgreSQL optimizer to eliminate partitions from the query
> when you run queries which include a predicate on the partitioning_col, e.g.
>
> select count(*) from bigtable where idate >= 200409131000
>
> will scan the last two partitions only...
>
> There are a few other ways of creating the view that return the same answer,
> but only using constants in that way will allow the partitions to be
> eliminated from the query, and so run for much longer.
Is there by any chance a set of functions to manage adding and removing
partitions? Certainly this can be done by hand, but having a set of
tools would make life much easier. I just looked but didn't see anything
on GBorg.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
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: decibel@decibel.org, simon@2ndquadrant.com, mcotner@yahoo.com, cbbrowne@acm.org
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
In-Reply-To: <20040914223333.GL56059@decibel.org>
* 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