Skip site navigation (1) Skip section navigation (2)

Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables

From: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org,Mark Cotner <mcotner(at)yahoo(dot)com>,Christopher Browne <cbbrowne(at)acm(dot)org>
Subject: Re: Data Warehouse Reevaluation - MySQL vs Postgres -- merge tables
Date: 2004-09-14 22:33:33
Message-ID: 20040914223333.GL56059@decibel.org (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)decibel(dot)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?"

In response to

Responses

pgsql-performance by date

Next:From: Jeffrey W. BakerDate: 2004-09-14 22:48:51
Subject: Re: disk performance benchmarks
Previous:From: Jim C. NasbyDate: 2004-09-14 21:45:40
Subject: Re: disk performance benchmarks

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group