Обсуждение: Partitioning Option?
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 **********************************************************************
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
Вложения
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
"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
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.
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
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
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=335width=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=189width=67) (actual time=1.507..83.662 rows=95 loops=1) Index Cond: (id_user = 4185) ->
IndexScan 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.206rows=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.363rows=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.95rows=2008190 width=67) (actual time=0.024..18031.218 rows=2008190 loops=1) -> Seq Scan on
user_logs_2002_hu (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_keyon 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