Re: [PROPOSAL] Table Partition - Mailing list pgsql-hackers
| From | My Life |
|---|---|
| Subject | Re: [PROPOSAL] Table Partition |
| Date | |
| Msg-id | tencent_364C8C650914F1D36CB720A1@qq.com Whole thread Raw |
| In response to | Re: [PROPOSAL] Table Partition (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
| List | pgsql-hackers |
> There is already a recent proposal on hackers about partition support in PostgreSQL by Amit Langote.
> You will find the thread at http://www.postgresql.org/message-id/55D3093C.5010800@lab.ntt.co.jp.
Actually, I have seen this design before, and it was not just a design, it has been implemented. I agree with it although I still have some reservations, because I think it is a little more complicated.
1. you store all partition info into 2 system catalogs: pg_partitioned_rel, pg_partition. it will be less efficient to access and maintain the partition info, include scan, add, delete, modify action, maybe concurrency. And the data volumn will get larger and larger.
2. the column 'anyarray partrangebounds' in pg_partition is not very accurate, and we cannot use the index access operators associated with some data type.
> In your design, can index scan be used for individual partition? If yes,
> can you share how it is handled?
of course, index scan can be used for individual partition.
because we make a unique constraint on partkey1, ..., partkeyN on pg_partition.pg_partition_2586.
the unique constraint is really a unique index.
if a index scan's index condition involved the partkey, we can use this unique index to choose the matched partitions in a specified order. and expand the partitioned table's index scan node into a list of partition's index scan node. In this way, the 'PartitionExpand' plan node likes the 'Append' plan node, but has some difference.
------------------ Original ------------------
> You will find the thread at http://www.postgresql.org/message-id/55D3093C.5010800@lab.ntt.co.jp.
Actually, I have seen this design before, and it was not just a design, it has been implemented. I agree with it although I still have some reservations, because I think it is a little more complicated.
1. you store all partition info into 2 system catalogs: pg_partitioned_rel, pg_partition. it will be less efficient to access and maintain the partition info, include scan, add, delete, modify action, maybe concurrency. And the data volumn will get larger and larger.
2. the column 'anyarray partrangebounds' in pg_partition is not very accurate, and we cannot use the index access operators associated with some data type.
> In your design, can index scan be used for individual partition? If yes,
> can you share how it is handled?
of course, index scan can be used for individual partition.
because we make a unique constraint on partkey1, ..., partkeyN on pg_partition.pg_partition_2586.
the unique constraint is really a unique index.
if a index scan's index condition involved the partkey, we can use this unique index to choose the matched partitions in a specified order. and expand the partitioned table's index scan node into a list of partition's index scan node. In this way, the 'PartitionExpand' plan node likes the 'Append' plan node, but has some difference.
------------------ Original ------------------
From: "Ashutosh Bapat";<ashutosh.bapat@enterprisedb.com>;
Date: Mon, Aug 31, 2015 02:43 PM
To: "My Life"<life.show@qq.com>;
Copy: "pgsql-hackers"<pgsql-hackers@postgresql.org>; "tgl"<tgl@sss.pgh.pa.us>; "bruce"<bruce@momjian.us>; "robertmhaas"<robertmhaas@gmail.com>;
Subject: Re: [HACKERS] Proposal of Table Partition
There is already a recent proposal on hackers about partition support in PostgreSQL by Amit Langote. You will find the thread at http://www.postgresql.org/message-id/55D3093C.5010800@lab.ntt.co.jp. May be you can collaborate with the ongoing work.
------------------ Original ------------------
From: "Amit Langote";<Langote_Amit_f8@lab.ntt.co.jp>;
Date: Mon, Aug 31, 2015 03:14 PM
To: "My Life"<life.show@qq.com>; "pgsql-hackers"<pgsql-hackers@postgresql.org>;
Subject: Re: [HACKERS] [PROPOSAL] Table Partition
Hello,
On 2015-08-30 PM 10:42, My Life wrote:
>
> For partitioned table's scan action, and JOIN action, we implemented
> a plan node named 'PartitionExpand'. the plan node can expand the
> partitioned table scan node into a list of partitions according to
> the filter and conditions. and it can expand partitioned table JOIN
> node into a list of partitions JOIN node wisely.
> We implemented a DynamicPrunePartition method, which can expand the
> partitioned table's scan node into a list of partition's scan node.
> We implemented a DynamicPrunePartitionJoin method, which can expand
> the partitioned table's JOIN node into a list of partition's JOIN node.
> These expand action happend in ExecInitPartitionExpand function, when
> initialize the executor. and all these action implemented based on the
> partition catalog.
>
In your design, can index scan be used for individual partition? If yes,
can you share how it is handled?
Thanks,
Amit
pgsql-hackers by date: