Execution Plans
The following SQL statement is used as an example:
1 2 3 4 5 |
gaussdb=# DROP TABLE IF EXISTS t1; gaussdb=# DROP TABLE IF EXISTS t2; gaussdb=# CREATE TABLE t1 (c1 int, c2 int); gaussdb=# CREATE TABLE t2 (c1 int, c2 int); gaussdb=# SET explain_perf_mode = pretty; |
gaussdb=# EXPLAIN SELECT * FROM t1,t2 WHERE t1.c1 = t2.c2; id | operation | E-rows | E-width | E-costs ----+--------------------------+--------+---------+----------------- 1 | -> Hash Join (2,3) | 23091 | 16 | 58.353..355.674 2 | -> Seq Scan on t1 | 2149 | 8 | 0.000..31.490 3 | -> Hash | 2149 | 8 | 31.490..31.490 4 | -> Seq Scan on t2 | 2149 | 8 | 0.000..31.490 (4 rows) Predicate Information (identified by plan id) ----------------------------------------------- 1 --Hash Join (2,3) Hash Cond: (t1.c1 = t2.c2) (2 rows) -- Drop the table. gaussdb=# DROP TABLE t1,t2;
- Interpretation of the execution plan columns (horizontal):
- id: execution operator node ID.
- operation: name of an execution operator.
- E-rows: number of output rows estimated by each operator.
- E-width: estimated width of an output tuple of each operator.
- E-costs: execution cost estimated by each operator.
- E-costs is measured by the optimizer based on an overhead unit. Usually, fetching a disk page sequence is defined as a unit. Other overhead parameters are set based on the unit.
- The overhead of each node (specified by E-costs) includes the overheads of all its child nodes.
- Such an overhead reflects only what the optimizer is concerned about, but does not consider the time for passing result rows to the client. Although the time may play an important role in the actual total time, it is ignored by the optimizer because it cannot be changed by modifying the plan.
- Interpretation of the execution plan layers (vertical):
- Layer 1: Seq Scan on t2
The table scan operator scans the table t2 using Seq Scan. At this layer, data in the table t2 is read from a buffer or disk, and then transferred to the upper-layer node for calculation.
- Layer 2: Hash
Hash operator. It is used to calculate the hash value of the operator transferred from the lower layer for subsequent hash join operations.
- Layer 3: Seq Scan on t1
The table scan operator scans the table t1 using Seq Scan. At this layer, data in the table t1 is read from a buffer or disk, and then transferred to the upper-layer node for hash join calculation.
- Layer 4: Hash Join
Join operator. It is used to join data in the t1 and t2 tables using the hash join method and output the result data.
- Layer 1: Seq Scan on t2
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