GI operators are used in parallel execution to iterate a table by partition or by data block.
Based on the granularity of data iteration, GI operators fall into the following types: PX PARTITION ITERATOR and PX BLOCK ITERATOR.
PX PARTITION ITERATOR
The PX PARTITION ITERATOR operator iterates data at the partition level.
In the following example, Operator 2 iterates data at the partition level.
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 shows in detail the output information of the PX PARTITION ITERATOR operator.
| Field | Description |
|---|---|
| output | The output expressions of the operator. |
| filter | The filter conditions of the operator. In this example, filter is set to nil because no filter condition is configured for the PX PARTITION ITERATOR operator. |
PX BLOCK ITERATOR
The PX BLOCK ITERATOR operator iterates data at the block level.
The PX BLOCK ITERATOR operator iterates data at the block level, which takes a finer-grained perspective compared with the PX PARTITION ITERATOR operator. Therefore, the PX BLOCK ITERATOR operator can divide finer tasks and supports a higher degree of 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 shows in detail the output information of the PX BLOCK ITERATOR operator. The fields of this operator have the same meaning as those of the PX PARTITION ITERATOR operator.