Re: partitionning

Hi,

On Sat, 12 Mar 2005, Mike Rylander wrote:

> Back to the original question on this thread, and using PG 8.0.1.
> Perhaps someone would like to poke holes in this (other than the need
> to set up fkeys and indexes on the inherited tables...):
>
> -------------------------------------------------------------------------------
>
> begin;
> create schema partition_test;
> set search_path to partition_test,public;
>
> create table test_base ( id serial, value text, partitioner timestamp
> with time zone default now());
> create table test_2005_03 () inherits (test_base);
> create table test_2005_04 () inherits (test_base);
>
> create rule base_partitioner_test_2005_03 as on insert
>        to test_base where partitioner AT TIME ZONE 'UTC' between
> '2005-03-01'::timestamp and '2005-04-01'::timestamp
>                do instead insert into test_2005_03 values (NEW.*);
>
> create rule base_partitioner_test_2005_04 as on insert
>        to test_base where partitioner AT TIME ZONE 'UTC' between
> '2005-04-01'::timestamp and '2005-05-01'::timestamp
>                do instead insert into test_2005_04 values (NEW.*);
>
> insert into test_base (value) values ('first string');
> insert into test_base (value, partitioner) values ('a string',
> '2004-01-30 10:17:08');
> insert into test_base (value, partitioner) values ('a string',
> '2005-04-01 14:17:08');
>
> explain analyze select * from test_base;
> select tableoid::regclass,* from test_base;
>
> rollback;
>
> -------------------------------------------------------------------------------

any ideas how we would get

     select * from test_base where partitioner between a and b

to just look in the correct partition and not doing a full sweep over
the other partitions or the base.

Greetings
Christian

--
Christian Kratzer                       ck@cksoft.de
CK Software GmbH                        http://www.cksoft.de/
Phone: +49 7452 889 135                 Fax: +49 7452 889 136