The GI operator is used in parallel execution to iterate through an entire table by partition or by data block.
Based on the granularity of the data being iterated over, the GI operators include PX PARTITION ITERATOR and PX BLOCK ITERATOR.
PX PARTITION ITERATOR
The PX PARTITION ITERATOR operator is used to iterate through data at the partition granularity.
In the following example, operator 2 iterates through data at the partition granularity.
obclient> CREATE TABLE t (c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> CREATE INDEX idx ON t (c1);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT /*+FULL(t)*/ c1 FROM t;
Query Plan:
======================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------
|0 |PX COORDINATOR | |400000 |427257|
|1 | EXCHANGE OUT DISTR |:EX10000|400000 |247403|
|2 | PX PARTITION ITERATOR| |400000 |247403|
|3 | TABLE SCAN |T |400000 |247403|
======================================================
Outputs & filters:
-------------------------------------
0 - output([T.C1], [T.C2]), filter(nil)
1 - output([T.C1], [T.C2]), filter(nil), dop=1
2 - output([T.C1], [T.C2]), filter(nil)
3 - output([T.C1], [T.C2]), filter(nil),
access([T.C1], [T.C2]), partitions(p[0-3])
In the preceding example, the outputs & filters section of the execution plan shows the output information of the PX PARTITION ITERATOR operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter condition of this operator. In the example, no filter is set for the PX PARTITION ITERATOR operator, so the value is nil. |
PX BLOCK ITERATOR
The PX BLOCK ITERATOR operator is used to iterate through data at the data block granularity.
Compared with the PX PARTITION ITERATOR, the PX BLOCK ITERATOR operator iterates through data in smaller blocks, allowing it to split into more tasks and support higher parallelism.
obclient> EXPLAIN SELECT /*+PARALLEL(4)*/ c1 FROM t;
Query Plan:
==================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------------
|0 |PX COORDINATOR | |400000 |279171|
|1 | EXCHANGE OUT DISTR|:EX10000|400000 |189244|
|2 | PX BLOCK ITERATOR| |400000 |189244|
|3 | TABLE SCAN |T(IDX) |400000 |189244|
==================================================
Outputs & filters:
-------------------------------------
0 - output([T.C1]), filter(nil)
1 - output([T.C1]), filter(nil), dop=4
2 - output([T.C1]), filter(nil)
3 - output([T.C1]), filter(nil),
access([T.C1]), partitions(p[0-3])
In the preceding example, the outputs & filters section of the execution plan shows the output information of the PX BLOCK ITERATOR operator. The fields have the same meaning as those of the PX PARTITION ITERATOR operator.