Re:

From: "Matt Casters" <Matt(dot)Casters(at)advalvas(dot)be>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re:
Date: 2005-01-21 08:50:46
Message-ID: 12670.193.190.212.113.1106297446.squirrel@193.190.212.113
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


> On Thu, Jan 20, 2005 at 11:31:29 -0500,
> Alex Turner <armtuk(at)gmail(dot)com> wrote:
>> I am curious - I wasn't aware that postgresql supported partitioned tables,
>> Could someone point me to the docs on this.
>
> Some people have been doing it using a union view. There isn't actually
> a partition feature.
>
>

Actually, there is. If found this example on pgsql-performance:

>> CREATE TABLE super_foo ( partition NUMERIC, bar NUMERIC );
>> ANALYZE super_foo ;
>>
>> CREATE TABLE sub_foo1 () INHERITS ( super_foo );
>> INSERT INTO sub_foo1 VALUES ( 1, 1 );
>> -- repeat insert until sub_foo1 has 1,000,000 rows
>> CREATE INDEX idx_subfoo1_partition ON sub_foo1 ( partition );
>> ANALYZE sub_foo1 ;
>>
>> CREATE TABLE sub_foo2 () INHERITS ( super_foo );
>> INSERT INTO sub_foo2 VALUES ( 2, 1 );
>> -- repeat insert until sub_foo2 has 1,000,000 rows
>> CREATE INDEX idx_subfoo2_partition ON sub_foo2 ( partition );
>> ANALYZE sub_foo2 ;
>>

I think that in certain cases this system even beats Oracle as it stores less information in the
table partitions. (and in doing so is causing less disk IO)
BTW, internally, Oracle sees partitions as tables too. Even the "Union all" system that MS SQL
Server uses works fine as long as the optimiser supports it to prune correctly.

Cheers,

Matt
------
Matt Casters <matt(dot)casters(at)ibridge(dot)be>
i-Bridge bvba, http://www.kettle.be
Fonteinstraat 70, 9400 Okegem, Belgium
Phone +32 (0) 486/97.29.37

In response to

  • Re: at 2005-01-21 00:14:27 from Bruno Wolff III

Responses

  • Re: at 2005-01-21 12:30:08 from Yann Michel

Browse pgsql-performance by date

  From Date Subject
Next Message Matt Clark 2005-01-21 09:16:08 Re: PostgreSQL clustering VS MySQL clustering
Previous Message ken 2005-01-21 08:19:05 inheritance performance