Partition Iterator
Description
Table partitioning logically divides a large table or index into smaller and easier-to-manage logical units (partitions), minimizing the impact on table query and modification statements. Users can quickly locate a partition where data is located by using a partition key. In this way, users do not need to scan all large tables in the database and can concurrently perform DDL and DML operations on different partitions. GaussDB supports three partitioning policies: range partitioning, hash partitioning, and list partitioning. Range partitioning is implemented based on binary-search, and the complexity is O(logN). Hash partitioning and list partitioning are implemented based on the key-partOid hash table, and the complexity is O(1).
Typical Scenarios
- Create a level-2 partitioned table using range partitioning.
- Create a level-2 partitioned table using hash partitioning.
- Create a level-2 partitioned table using list partitioning.
Examples
Example 1: Create a level-2 partitioned table using range partitioning.
-- Prepare data. gaussdb=# CREATE TABLE t1 (c1 int, c2 int) PARTITION BY RANGE (c1) SUBPARTITION BY RANGE (c1) ( PARTITION p1 VALUES LESS THAN(10) ( SUBPARTITION subp1_1 VALUES LESS THAN(5), SUBPARTITION subp1_2 VALUES LESS THAN(10) ), PARTITION p2 VALUES LESS THAN(20) ( SUBPARTITION subp2_1 VALUES LESS THAN(15), SUBPARTITION subp2_2 VALUES LESS THAN(20) ), PARTITION p3 VALUES LESS THAN(MAXVALUE) ( SUBPARTITION subp3_1 VALUES LESS THAN(30), SUBPARTITION subp3_2 VALUES LESS THAN(MAXVALUE) ) ); CREATE TABLE gaussdb=# INSERT INTO t1 VALUES(generate_series(1,100,2), 1); INSERT 0 50 gaussdb=# NSERT INTO t1 VALUES(generate_series(2,101,2), 2); INSERT 0 50 -- Execution result. gaussdb=# EXPLAIN SELECT * FROM t1 WHERE c1 < 15 AND c2 <= 1; QUERY PLAN ----------------------------------------------------------------------- Partition Iterator (cost=0.00..33.23 rows=239 width=8) Iterations: 2, Sub Iterations: 3 -> Partitioned Seq Scan on t1 (cost=0.00..33.23 rows=239 width=8) Filter: ((c1 < 15) AND (c2 <= 1)) Selected Partitions: 1..2 Selected Subpartitions: 1:1..2 2:1 (6 rows)) -- Drop. gaussdb=# DROP TABLE t1;
In the preceding example, the output of the Partition Iterator operator is as follows.
Item |
Description |
---|---|
Partition Iterator |
Operator name. |
Iterations |
Specifies the number of iterations performed by the partition iteration operator on level-1 partitions. If PART is displayed, dynamic pruning is used. For example, Iterations: 2 in example 1 indicates that the iteration operator needs to traverse two level-1 partitions. Iterations: PART indicates that the number of level-1 partitions to be traversed is determined by parameter conditions of the partition key. |
Sub Iterations |
Specifies the number of iterations performed by the partition iteration operator on level-2 partitions. If PART is displayed, dynamic pruning is used. For example, Sub Iterations: 3 in example 1 indicates that the iteration operator needs to traverse three level-2 partitions. Iterations: PART indicates that the number of level-2 partitions to be traversed is determined by parameter conditions of the partition key. |
Filter |
Filter predicate of the operator. In the example, the filter condition is that the value in column c1 is less than 15 and the value in column c2 is less than or equal to 1. When a query is executed, rows that meet these conditions are included in the final result set. |
Partitioned Seq Scan |
Scan mode of a partitioned table. |
Selected Partitions |
Level-1 partition pruning result. In example 1, Selected Partitions: 1..2 indicates that partitions 1 and 2 are selected. |
Selected Subpartitions |
Level-2 partition pruning result. In example 1, Selected Subpartitions: 1:1..2 2:1 indicates that subpartitions 1 and 2 of the first level-1 partition and partition 1 of the second level-1 partition are selected. Selected Subpartitions: ALL indicates that all level-2 partitions are selected. |
Example 2: Create a level-2 partitioned table using hash partitioning.
-- Prepare data. gaussdb=# CREATE TABLE sales (id INT,number INT) PARTITION BY HASH (number) SUBPARTITION BY HASH (id) ( PARTITION p0 ( SUBPARTITION sp0_0, SUBPARTITION sp0_1 ), PARTITION p1 ( SUBPARTITION sp1_0, SUBPARTITION sp1_1 ) ); CREATE TABLE gaussdb=# INSERT INTO sales VALUES(generate_series(1,50), generate_series(1,50)); INSERT 0 50 -- Execution result. gaussdb=# EXPLAIN SELECT * FROM sales WHERE id < 15 AND number <= 50; QUERY PLAN -------------------------------------------------------------------------- Partition Iterator (cost=0.00..42.23 rows=239 width=8) Iterations: 2, Sub Iterations: 4 -> Partitioned Seq Scan on sales (cost=0.00..42.23 rows=239 width=8) Filter: ((id < 15) AND ("number" <= 50)) Selected Partitions: 1..2 Selected Subpartitions: ALL (6 rows) -- Drop. gaussdb=# DROP TABLE sales;
In the preceding example, the output of the Partition Iterator operator is as follows.
Item |
Description |
---|---|
Partition Iterator |
Operator name. |
Iterations |
Specifies the number of iterations performed by the partition iteration operator on level-1 partitions. If PART is displayed, dynamic pruning is used. For example, Iterations: 2 in example 2 indicates that the iteration operator needs to traverse two level-1 partitions. Iterations: PART indicates that the number of level-1 partitions to be traversed is determined by parameter conditions of the partition key. |
Sub Iterations |
Specifies the number of iterations performed by the partition iteration operator on level-2 partitions. If PART is displayed, dynamic pruning is used. For example, Sub Iterations: 4 in example 1 indicates that the iteration operator needs to traverse four level-2 partitions. Iterations: PART indicates that the number of level-2 partitions to be traversed is determined by parameter conditions of the partition key. |
Filter |
Filter predicate of the operator. In the example 2, the filter condition is that the value in column id is less than 15 and the value in column number is less than or equal to 50. When a query is executed, rows that meet these conditions are included in the final result set. |
Partitioned Seq Scan |
Scan mode of a partitioned table. |
Selected Partitions |
Level-1 partition pruning result. In example 2, Selected Partitions: 1..2 indicates that partitions 1 and 2 are selected. |
Selected Subpartitions |
Level-2 partition pruning result. Selected Subpartitions: ALL in example 2 indicates that all level-2 partitions are selected. |
Example 3: Create a level-2 partitioned table using list partitioning.
-- Prepare data. gaussdb=# CREATE TABLE list_partitioned_table (id INT,category INT) PARTITION BY LIST (category) SUBPARTITION BY LIST (id) ( PARTITION p0 VALUES (1, 3, 5, 7) ( SUBPARTITION sp0_0 VALUES (0), SUBPARTITION sp0_1 VALUES (1) ), PARTITION p1 VALUES (2, 4, 6, 8) ( SUBPARTITION sp1_0 VALUES (0), SUBPARTITION sp1_1 VALUES (1) ) ); CREATE TABLE gaussdb=# INSERT INTO list_partitioned_table VALUES(0,generate_series(1,8,2)); INSERT 0 4 gaussdb=# INSERT INTO list_partitioned_table VALUES(1,generate_series(2,7,2)); INSERT 0 3 -- Execution result. gaussdb=# EXPLAIN SELECT * FROM list_partitioned_table WHERE id = 0 AND category <= 5; QUERY PLAN ----------------------------------------------------------------------------------------- Partition Iterator (cost=0.00..42.23 rows=4 width=8) Iterations: 2, Sub Iterations: 2 -> Partitioned Seq Scan on list_partitioned_table (cost=0.00..42.23 rows=4 width=8) Filter: ((category <= 5) AND (id = 0)) Selected Partitions: 1..2 Selected Subpartitions: 1:1 2:1 (6 rows) -- Drop. gaussdb=# DROP TABLE list_partitioned_table;
In the preceding example, the output of the Partition Iterator operator is as follows.
Item |
Description |
---|---|
Partition Iterator |
Operator name. |
Iterations |
Specifies the number of iterations performed by the partition iteration operator on level-1 partitions. If PART is displayed, dynamic pruning is used. For example, Iterations: 2 in example 3 indicates that the iteration operator needs to traverse two level-1 partitions. Iterations: PART indicates that the number of level-1 partitions to be traversed is determined by parameter conditions of the partition key. |
Sub Iterations |
Specifies the number of iterations performed by the partition iteration operator on level-2 partitions. If PART is displayed, dynamic pruning is used. For example, Sub Iterations: 2 in example 3 indicates that the iteration operator needs to traverse two level-2 partitions. Iterations: PART indicates that the number of level-2 partitions to be traversed is determined by parameter conditions of the partition key. |
Filter |
Filter predicate of the operator. In the example 2, the filter condition is that the value in column category is less than or equal to 5 and the value in column id is equal to 0. When a query is executed, rows that meet these conditions are included in the final result set. |
Partitioned Seq Scan |
Scan mode of a partitioned table. |
Selected Partitions |
Level-1 partition pruning result. In example 3, Selected Partitions: 1..2 indicates that partitions 1 and 2 are selected. |
Selected Subpartitions |
Level-2 partition pruning result. In example 3, Selected Subpartitions: 1:1 2:1 indicates that subpartition 1 of the first level-1 partition and subpartition 1 of the second level-1 partition are selected. Selected Subpartitions: ALL indicates that all level-2 partitions are selected. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot