The EXPLAIN request modifier also reports whether operations are pipelined. That is, whether an operation passes its result directly to a consumer operation, without temporarily storing the result on a spool. Pipelining reduces spool usage, which improves query and system performance and may also reduce storage cost.
Pipeline Block Characteristics
- In a pipeline block, steps are dependent. Steps must run simultaneously, because the rows flow from the first step to the last.
In contrast, steps in a parallel block are independent, and need not run simultaneously.
- The more steps a pipeline block has, the better the query performance.
Parallel computation and memory needs increase.
- The number of steps in a pipeline block depends on factors such as qualification of steps for pipelining and allowed pipeline depth.
Pipeline Left Sort of Merge Join Example
EXPLAIN SELECT * FROM t128k x, t64k y WHERE x.a = y.a ORDER BY 1;
Explanation
---------------------------------------------------------
1. First, we lock SB.x in TD_MAP1 for read on a reserved RowHash to
prevent global deadlock.
2. Next, we lock SB.y in TD_MAP1 for read on a reserved RowHash to
prevent global deadlock.
3. We lock SB.x in TD_MAP1 for read, and we lock SB.y in TD_MAP1 for
read.
4. We execute the following steps in a pipeline.
a. We do an all-AMPs RETRIEVE step in TD_MAP1 from SB.y by way
of an all-rows scan with no residual conditions into Pipeline
2 (all_amps), which is built locally on the AMPs. The size
of Pipeline 2 is estimated with low confidence to be 65,460
rows (8,378,880 bytes). The estimated time for this step is
0.15 seconds.
b. We do an all-AMPs JOIN step in TD_MAP1 from Pipeline 2 (Last
Use) by way of a RowHash match scan, which is joined to SB.x
by way of a RowHash match scan. Pipeline 2 and SB.x are
joined using a merge join, with a join condition of ("SB.x.a
= a"). The result goes into Spool 1 (group_amps), which is
built locally on the AMPs. Then we do a SORT to order Spool
1 by the sort key in spool field1 (SB.x.a). The size of
Spool 1 is estimated with low confidence to be 87,280 rows (
29,587,920 bytes). The estimated time for this step is 0.78
seconds.
5. Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
Pipeline Right Sort of Merge Join Example
EXPLAIN SELECT T1.I5,T2.I5 FROM T1000_A AS T1, T1000_B AS T2 WHERE T1.I5 = T2.I5;
Explanation
---------------------------------------------------------
1. First, we lock HP_DB_P3.T2 in TD_MAP1 for read on a reserved
RowHash to prevent global deadlock.
2. Next, we lock HP_DB_P3.T1 in TD_MAP1 for read on a reserved
RowHash to prevent global deadlock.
3. We lock HP_DB_P3.T2 in TD_MAP1 for read, and we lock HP_DB_P3.T1
in TD_MAP1 for read.
4. We do an all-AMPs BULK RETRIEVE step in TD_MAP1 from HP_DB_P3.T2
by way of an all-rows scan with a condition of ("NOT
(HP_DB_P3.T2.I5 IS NULL)") into Spool 2 (all_amps), which is
redistributed by the hash code of (HP_DB_P3.T2.I5) to all AMPs in
TD_MAP1. Then we do a SORT to order Spool 2 by row hash. The
size of Spool 2 is estimated with high confidence to be 1,000 rows
(17,000 bytes). The estimated time for this step is 0.01 seconds.
5. We execute the following steps in a pipeline.
a. We do an all-AMPs BULK RETRIEVE step in TD_MAP1 from
HP_DB_P3.T1 by way of an all-rows scan with a condition of (
"NOT (HP_DB_P3.T1.I5 IS NULL)") into Pipeline 3 (all_amps),
which is redistributed by the hash code of (HP_DB_P3.T1.I5)
to all AMPs in TD_MAP1. Then we do a SORT to order Pipeline
3 by row hash. The size of Pipeline 3 is estimated with high
confidence to be 1,000 rows (17,000 bytes). The estimated
time for this step is 0.01 seconds.
b. We do an all-AMPs JOIN step in TD_MAP1 from Spool 2 (Last
Use) by way of a RowHash match scan, which is joined to
Pipeline 3 (Last Use) by way of a RowHash match scan. Spool
2 and Pipeline 3 are joined using a merge join, with a join
condition of ("I5 = I5"). The result goes into Spool 1
(group_amps), which is built locally on the AMPs. The size
of Spool 1 is estimated with no confidence to be 31,623 rows
(1,359,789 bytes). The estimated time for this step is 0.03
seconds.
6. Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.