|
|
 | | From: | Matt Casters | | Subject: | Re: | | Date: | Fri, 21 Jan 2005 09:50:46 +0100 (CET) |
|
|
 |
> On Thu, Jan 20, 2005 at 11:31:29 -0500,
> Alex Turner 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
i-Bridge bvba, http://www.kettle.be
Fonteinstraat 70, 9400 Okegem, Belgium
Phone +32 (0) 486/97.29.37
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
|
|
| | From: | Yann Michel | | Subject: | Re: | | Date: | Fri, 21 Jan 2005 13:30:08 +0100 |
|
|
| Hi,
On Fri, Jan 21, 2005 at 09:50:46AM +0100, Matt Casters wrote:
>
> > 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 );
[...]
> >>
> >> CREATE TABLE sub_foo2 () INHERITS ( super_foo );
[...]
> >>
Yes, this could be used instead of a view. But there is one thing
missing. You can't just insert into super_foo and aquire the "correct
partition". You will still have to insert into the correct underlying
table. "Real" partitioning will take care of correct partition
selection.
Regards,
Yann
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
| | From: | Matteo Beccati | | Subject: | Re: | | Date: | Fri, 21 Jan 2005 15:37:20 +0100 |
|
|
| Hi,
>>>> CREATE TABLE super_foo ( partition NUMERIC, bar NUMERIC );
>>>> ANALYZE super_foo ;
>>>>
>>>> CREATE TABLE sub_foo1 () INHERITS ( super_foo );
>>>> CREATE TABLE sub_foo2 () INHERITS ( super_foo );
>
> Yes, this could be used instead of a view. But there is one thing
> missing. You can't just insert into super_foo and aquire the "correct
> partition". You will still have to insert into the correct underlying
> table. "Real" partitioning will take care of correct partition
> selection.
I've recently used this method for partitioning data. In my setup
inserts are done inside a pl/pgsql function called at regular intervals,
so this isn't a problem for me. I didn't test it, but I think some rules
(or a trigger) could do the trick.
Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
|
|
| | From: | Yann Michel | | Subject: | Re: | | Date: | Fri, 21 Jan 2005 17:05:39 +0100 |
|
|
| Hi,
On Fri, Jan 21, 2005 at 03:37:20PM +0100, Matteo Beccati wrote:
>
> >>>> CREATE TABLE super_foo ( partition NUMERIC, bar NUMERIC );
> >>>> ANALYZE super_foo ;
> >>>>
> >>>> CREATE TABLE sub_foo1 () INHERITS ( super_foo );
> >>>> CREATE TABLE sub_foo2 () INHERITS ( super_foo );
> >
> >Yes, this could be used instead of a view. But there is one thing
> >missing. You can't just insert into super_foo and aquire the "correct
> >partition". You will still have to insert into the correct underlying
> >table. "Real" partitioning will take care of correct partition
> >selection.
>
> I've recently used this method for partitioning data. In my setup
> inserts are done inside a pl/pgsql function called at regular intervals,
> so this isn't a problem for me. I didn't test it, but I think some rules
> (or a trigger) could do the trick.
Yes, a pl/pgsql function or any software solution can solve this
problem, but what you normally expect from a partitioning support is
that you don't have to care about where to put your data due to the db
will take care for that.
Of cause a trigger could do this as well, but don't forget, that a
trigger in dwh environments, where you process thousands of row at once
during data loading, is very expensive and therefore no solution for
production use.
Regards,
Yann
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
|
|
|
