Re: partitionning - Mailing list pgsql-general

On Sat, 12 Mar 2005 17:39:38 +0100 (CET), Christian Kratzer
<ck-lists@cksoft.de> wrote:
> 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.

There is now way to have PG completely skip the unused partitions.
However, with an index on the "partitioner" column of each partition
table PG will basically fall of the end of the index almost
imediately, and then use an appropiate plan for the useful partitions.
 It's not the same as Oracle partitioning, but without expicit Planner
work it can't be.

It should be about the same speed as one gigantic table, perhaps
faster for some queries where there are many total partitions and few
useful ones, but for my money that's not where the biggest benefit of
partitioning lies.  My favorite feature of classic partitioning is
that you can load and unload large chunks of the data without
affecting everyone else;  you can very easily archive unused
partitions or transfer them to a statistics server for data mining
with no impact on other writers.

Another benefit would to be the ability to put the currently
write-active partition on a very fast/expensive storage area and move
the older data to a slower/cheaper storage area using tablespaces.

--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org