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 partitioned table using range partitioning.
- Create a partitioned table using hash partitioning.
- Create a partitioned table using list partitioning.
Examples
Example 1: Create a partitioned table using range partitioning.
-- Prepare data. gaussdb=#DROP TABLE IF EXISTS t1; gaussdb=# SET max_datanode_for_plan = 1; gaussdb=#CREATE TABLE t1 (c1 int, c2 int) PARTITION BY RANGE (c1)( PARTITION p1 VALUES LESS THAN(10) , PARTITION p2 VALUES LESS THAN(20), PARTITION p3 VALUES LESS THAN(MAXVALUE) ); CREATE TABLE gaussdb=#INSERT INTO t1 VALUES(generate_series(1,100,2), 1); INSERT 0 50 gaussdb=#INSERT 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 ------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT c1, c2 FROM public.t1 WHERE c1 < 15 AND c2 <= 1 Datanode Name: datanode1 Partition Iterator (cost=0.00..33.23 rows=239 width=8) Iterations: 2 -> Partitioned Seq Scan on t1 (cost=0.00..33.23 rows=239 width=8) Filter: ((c1 < 15) AND (c2 <= 1)) Selected Partitions: 1..2 (11 rows) -- Drop. gaussdb=#DROP TABLE IF EXISTS 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. |
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. |
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); 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 ------------------------------------------------------------------------------------ Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: All datanodes Remote SQL: SELECT id, "number" FROM public.sales WHERE id < 15 AND "number" <= 50 Datanode Name: datanode1 Partition Iterator (cost=0.00..42.23 rows=239 width=8) Iterations: 1 -> Partitioned Seq Scan on sales (cost=0.00..42.23 rows=239 width=8) Filter: ((id < 15) AND ("number" <= 50)) Selected Partitions: 1 (11 rows) -- Drop. 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. |
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. |
Example 3: Create a partitioned table using list partitioning.
-- Prepare data. gaussdb=#CREATE TABLE list_partitioned_table (id INT, category INT) PARTITION BY LIST (category) ( PARTITION p0 VALUES (1, 3, 5, 7), PARTITION p1 VALUES (2, 4, 6, 8) ); 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 --------------------------------------------------------------------------------------------------- Data Node Scan (cost=0.00..0.00 rows=0 width=0) Node/s: datanode1 Remote SQL: SELECT id, category FROM public.list_partitioned_table WHERE id = 0 AND category <= 5 Datanode Name: datanode1 Partition Iterator (cost=0.00..42.23 rows=4 width=8) 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 (11 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. |
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. |
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