pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitioning Option?
8+ messages / 5 participants
[nested] [flat]
* Partitioning Option?
@ 2005-02-23 18:06 Tomeh, Husam <htomeh@firstam.com>
2005-02-23 18:49 ` Re: Partitioning Option? Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tomeh, Husam @ 2005-02-23 18:06 UTC (permalink / raw)
To: pgsql-admin
Does PostgreSQL support/allow "partitioning" of objects like tables and
indexes, like Oracle does?
I have searched the docs, but haven't found anything to partitioning.
Thanks,
--
Husam
**********************************************************************
This message contains confidential information intended only for the
use of the addressee(s) named above and may contain information that
is legally privileged. If you are not the addressee, or the person
responsible for delivering it to the addressee, you are hereby
notified that reading, disseminating, distributing or copying this
message is strictly prohibited. If you have received this message by
mistake, please immediately notify us by replying to the message and
delete the original message immediately thereafter.
Thank you. FADLD Tag
**********************************************************************
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitioning Option?
2005-02-23 18:06 Partitioning Option? Tomeh, Husam <htomeh@firstam.com>
@ 2005-02-23 18:49 ` Joshua D. Drake <jd@commandprompt.com>
2005-02-25 00:39 ` Re: Partitioning Option? Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 1 reply; 8+ messages in thread
From: Joshua D. Drake @ 2005-02-23 18:49 UTC (permalink / raw)
To: Tomeh, Husam <htomeh@firstam.com>; +Cc: pgsql-admin
Tomeh, Husam wrote:
>Does PostgreSQL support/allow "partitioning" of objects like tables and
>indexes, like Oracle does?
>
>
We support tablespaces but not table partitioning. You can get around
this by using namespaces and unions however.
Sincerely,
Joshua D. Drake
>I have searched the docs, but haven't found anything to partitioning.
>
>Thanks,
>
>--
> Husam
>**********************************************************************
>This message contains confidential information intended only for the
>use of the addressee(s) named above and may contain information that
>is legally privileged. If you are not the addressee, or the person
>responsible for delivering it to the addressee, you are hereby
>notified that reading, disseminating, distributing or copying this
>message is strictly prohibited. If you have received this message by
>mistake, please immediately notify us by replying to the message and
>delete the original message immediately thereafter.
>
>Thank you. FADLD Tag
>**********************************************************************
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
Attachments:
jd.vcf (text/x-vcard, 283B) download
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:jd@commandprompt.com
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitioning Option?
2005-02-23 18:06 Partitioning Option? Tomeh, Husam <htomeh@firstam.com>
2005-02-23 18:49 ` Re: Partitioning Option? Joshua D. Drake <jd@commandprompt.com>
@ 2005-02-25 00:39 ` Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 0 replies; 8+ messages in thread
From: Gaetano Mendola @ 2005-02-25 00:39 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>
Joshua D. Drake wrote:
> Tomeh, Husam wrote:
>
>> Does PostgreSQL support/allow "partitioning" of objects like tables and
>> indexes, like Oracle does?
>>
> We support tablespaces but not table partitioning. You can get around
> this by using namespaces and unions however.
I demonstrate that at least with 7.4.x the horizontal partition is not
applicable, see my post on performance ( "horizontal partition" )
As soon you use the view with all UNION joined with other table you loose
the index usage on that view :-(
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitioning Option?
@ 2005-02-23 18:54 Tomeh, Husam <htomeh@firstam.com>
2005-02-23 19:12 ` Re: Partitioning Option? Tom Lane <tgl@sss.pgh.pa.us>
2005-02-23 19:48 ` Re: Partitioning Option? Bruno Wolff III <bruno@wolff.to>
2005-02-25 00:40 ` Re: Partitioning Option? Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 3 replies; 8+ messages in thread
From: Tomeh, Husam @ 2005-02-23 18:54 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-admin
Thanks Josh, I'll check out the namespace concept.
(I was referring to object partitioning. For instance, if I have a huge
table with US counties as my partition key, I could create partitions
within the same table based on the partition key (a US county for
example). When querying, the engine will access the partition instead of
the whole table to get the result set. This is provided in Oracle DB EE.
So, I was wondering whether I can do similar thing in PostgreSQL since
we're exploring PostgreSQL)
--
Husam
-----Original Message-----
From: Joshua D. Drake [mailto:jd@commandprompt.com]
Sent: Wednesday, February 23, 2005 10:50 AM
To: Tomeh, Husam
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Partitioning Option?
Tomeh, Husam wrote:
>Does PostgreSQL support/allow "partitioning" of objects like tables and
>indexes, like Oracle does?
>
>
We support tablespaces but not table partitioning. You can get around
this by using namespaces and unions however.
Sincerely,
Joshua D. Drake
>I have searched the docs, but haven't found anything to partitioning.
>
>Thanks,
>
>--
> Husam
>**********************************************************************
>This message contains confidential information intended only for the
>use of the addressee(s) named above and may contain information that is
>legally privileged. If you are not the addressee, or the person
>responsible for delivering it to the addressee, you are hereby notified
>that reading, disseminating, distributing or copying this message is
>strictly prohibited. If you have received this message by mistake,
>please immediately notify us by replying to the message and delete the
>original message immediately thereafter.
>
>Thank you. FADLD Tag
>**********************************************************************
>
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to
>majordomo@postgresql.org
>
>
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitioning Option?
2005-02-23 18:54 Re: Partitioning Option? Tomeh, Husam <htomeh@firstam.com>
@ 2005-02-23 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-25 00:44 ` Re: Partitioning Option? Gaetano Mendola <mendola@bigfoot.com>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2005-02-23 19:12 UTC (permalink / raw)
To: Tomeh, Husam <htomeh@firstam.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin
"Tomeh, Husam" <htomeh@firstam.com> writes:
> (I was referring to object partitioning. For instance, if I have a huge
> table with US counties as my partition key, I could create partitions
> within the same table based on the partition key (a US county for
> example). When querying, the engine will access the partition instead of
> the whole table to get the result set. This is provided in Oracle DB EE.
> So, I was wondering whether I can do similar thing in PostgreSQL since
> we're exploring PostgreSQL)
You can build it out of spare parts: either a view over a UNION ALL of
component tables, or a parent table with a bunch of inheritance
children, either way with rules to redirect insertions into the
right subtable. (With the inheritance way you could instead use
a trigger for that, which'd likely be more flexible.)
It's not going to be quite as good as the Oracle facility, mainly
because the planner has no direct understanding that it's a partitioning
setup, but you can get most of the win.
See past discussions in the mailing list archives for details.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitioning Option?
2005-02-23 18:54 Re: Partitioning Option? Tomeh, Husam <htomeh@firstam.com>
2005-02-23 19:12 ` Re: Partitioning Option? Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-02-25 00:44 ` Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 0 replies; 8+ messages in thread
From: Gaetano Mendola @ 2005-02-25 00:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>
Tom Lane wrote:
> "Tomeh, Husam" <htomeh@firstam.com> writes:
>
>>(I was referring to object partitioning. For instance, if I have a huge
>>table with US counties as my partition key, I could create partitions
>>within the same table based on the partition key (a US county for
>>example). When querying, the engine will access the partition instead of
>>the whole table to get the result set. This is provided in Oracle DB EE.
>>So, I was wondering whether I can do similar thing in PostgreSQL since
>>we're exploring PostgreSQL)
>
>
> You can build it out of spare parts: either a view over a UNION ALL of
> component tables, or a parent table with a bunch of inheritance
> children, either way with rules to redirect insertions into the
> right subtable. (With the inheritance way you could instead use
> a trigger for that, which'd likely be more flexible.)
Tom, I did a post on performance about my attempt to do an horizontal partition,
in a 7.4.x engine, but it seems the planner refuse to optimize it,
look at this for example:
CREATE TABLE user_logs_2003_h () inherits (user_logs);
CREATE TABLE user_logs_2002_h () inherits (user_logs);
I defined on these tables the index already defined on user_logs.
And this is the result:
empdb=# explain analyze select * from user_logs where id_user = sp_id_user('kalman');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..426.33 rows=335 width=67) (actual time=20.891..129.218 rows=98 loops=1)
-> Append (cost=0.00..426.33 rows=335 width=67) (actual time=20.871..128.643 rows=98 loops=1)
-> Index Scan using idx_user_user_logs on user_logs (cost=0.00..133.11 rows=66 width=67) (actual time=20.864..44.594 rows=3 loops=1)
Index Cond: (id_user = 4185)
-> Index Scan using idx_user_user_logs_2003_h on user_logs_2003_h user_logs (cost=0.00..204.39 rows=189 width=67) (actual time=1.507..83.662 rows=95 loops=1)
Index Cond: (id_user = 4185)
-> Index Scan using idx_user_user_logs_2002_h on user_logs_2002_h user_logs (cost=0.00..88.83 rows=80 width=67) (actual time=0.206..0.206 rows=0 loops=1)
Index Cond: (id_user = 4185)
Total runtime: 129.500 ms
(9 rows)
that is good, but now look what happen in a view like this one ( where I join the view above ):
create view to_delete AS
SELECT v.login,
u.*
from user_login v,
user_logs u
where v.id_user = u.id_user;
empdb=# explain analyze select * from to_delete where login = 'kalman';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=4.01..65421.05 rows=143 width=79) (actual time=1479.738..37121.511 rows=98 loops=1)
Hash Cond: ("outer".id_user = "inner".id_user)
-> Append (cost=0.00..50793.17 rows=2924633 width=67) (actual time=21.391..33987.363 rows=2927428 loops=1)
-> Seq Scan on user_logs u (cost=0.00..7195.22 rows=411244 width=67) (actual time=21.385..5641.307 rows=414039 loops=1)
-> Seq Scan on user_logs_2003_h u (cost=0.00..34833.95 rows=2008190 width=67) (actual time=0.024..18031.218 rows=2008190 loops=1)
-> Seq Scan on user_logs_2002_h u (cost=0.00..8764.00 rows=505199 width=67) (actual time=0.005..5733.554 rows=505199 loops=1)
-> Hash (cost=4.00..4.00 rows=2 width=16) (actual time=0.195..0.195 rows=0 loops=1)
-> Index Scan using user_login_login_key on user_login v (cost=0.00..4.00 rows=2 width=16) (actual time=0.155..0.161 rows=1 loops=1)
Index Cond: ((login)::text = 'kalman'::text)
Total runtime: 37122.069 ms
(10 rows)
I did a similar attempt with UNION ALL but the result is the same.
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitioning Option?
2005-02-23 18:54 Re: Partitioning Option? Tomeh, Husam <htomeh@firstam.com>
@ 2005-02-23 19:48 ` Bruno Wolff III <bruno@wolff.to>
2 siblings, 0 replies; 8+ messages in thread
From: Bruno Wolff III @ 2005-02-23 19:48 UTC (permalink / raw)
To: Tomeh, Husam <htomeh@firstam.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; pgsql-admin
On Wed, Feb 23, 2005 at 10:54:50 -0800,
"Tomeh, Husam" <htomeh@firstam.com> wrote:
> Thanks Josh, I'll check out the namespace concept.
>
> (I was referring to object partitioning. For instance, if I have a huge
> table with US counties as my partition key, I could create partitions
> within the same table based on the partition key (a US county for
> example). When querying, the engine will access the partition instead of
> the whole table to get the result set. This is provided in Oracle DB EE.
> So, I was wondering whether I can do similar thing in PostgreSQL since
> we're exploring PostgreSQL)
You have to do it by hand and you won't get the full savings.
The idea is to create tables for each partition. Then create a UNION ALL
view of the tables, incorporating the key condition that applies for
each table.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Partitioning Option?
2005-02-23 18:54 Re: Partitioning Option? Tomeh, Husam <htomeh@firstam.com>
@ 2005-02-25 00:40 ` Gaetano Mendola <mendola@bigfoot.com>
2 siblings, 0 replies; 8+ messages in thread
From: Gaetano Mendola @ 2005-02-25 00:40 UTC (permalink / raw)
To: Tomeh, Husam <htomeh@firstam.com>
Tomeh, Husam wrote:
> Thanks Josh, I'll check out the namespace concept.
>
> (I was referring to object partitioning. For instance, if I have a huge
> table with US counties as my partition key, I could create partitions
> within the same table based on the partition key (a US county for
> example). When querying, the engine will access the partition instead of
> the whole table to get the result set. This is provided in Oracle DB EE.
> So, I was wondering whether I can do similar thing in PostgreSQL since
> we're exploring PostgreSQL)
Look on the performance list my post: "horizontal partition".
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-02-25 00:44 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-23 18:06 Partitioning Option? Tomeh, Husam <htomeh@firstam.com>
2005-02-23 18:49 ` Joshua D. Drake <jd@commandprompt.com>
2005-02-25 00:39 ` Gaetano Mendola <mendola@bigfoot.com>
2005-02-23 18:54 Re: Partitioning Option? Tomeh, Husam <htomeh@firstam.com>
2005-02-23 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-25 00:44 ` Gaetano Mendola <mendola@bigfoot.com>
2005-02-23 19:48 ` Bruno Wolff III <bruno@wolff.to>
2005-02-25 00:40 ` Gaetano Mendola <mendola@bigfoot.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox