# Additional Information in `EXPLAIN ANALYZE` Output | Attribute | Description | | :------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | **`actRows`** | Number of rows output by the operator. | | **`execution info`** | Detailed execution information of the operator. `time` represents the total wall time from entering the operator to leaving the operator, including the total execution time of all suboperators. If the operator is called many times by the parent operator then the time refers to the accumulated time. `loops` is the number of times the current operator is called by the parent operator. | | **`memory`** | Memory used by the operator. | | **`disk`** | Disk space used by the operator. | + Note: Some attributes and explain table columns are omitted for improved formatting ```5 tidb:db1> EXPLAIN ANALYZE SELECT SUM(pm.m_count)/COUNT(*) FROM -> (SELECT COUNT(m.name) m_count -> FROM universe.moons m -> RIGHT JOIN -> (SELECT p.id, p.name -> FROM universe.planet_categories c -> JOIN universe.planets p -> ON c.id = p.category_id AND c.name = 'Jovian') pc -> ON m.planet_id = pc.id -> GROUP BY pc.name) pm; +-----------------------------------------+.+---------+-----------+---------------------------+----------------------------------------------------------------+.+-----------+---------+ | id |.| actRows | task | access object | execution info |.| memory | disk | +-----------------------------------------+.+---------+-----------+---------------------------+----------------------------------------------------------------+.+-----------+---------+ | Projection_14 |.| 1 | root | | time:1.39ms, loops:2, RU:1.561975, Concurrency:OFF |.| 9.64 KB | N/A | | └─StreamAgg_16 |.| 1 | root | | time:1.39ms, loops:2 |.| 1.46 KB | N/A | | └─Projection_40 |.| 4 | root | | time:1.38ms, loops:4, Concurrency:OFF |.| 8.24 KB | N/A | | └─HashAgg_17 |.| 4 | root | | time:1.36ms, loops:4, partial_worker:{...}, final_worker:{...} |.| 82.1 KB | N/A | | └─HashJoin_19 |.| 25 | root | | time:1.29ms, loops:2, build_hash_table:{...}, probe:{...} |.| 2.25 KB | 0 Bytes | | ├─HashJoin_35(Build) |.| 4 | root | | time:1.08ms, loops:2, build_hash_table:{...}, probe:{...} |.| 25.7 KB | 0 Bytes | | │ ├─IndexReader_39(Build) |.| 1 | root | | time:888.5µs, loops:2, cop_task: {...} |.| 286 Bytes | N/A | | │ │ └─IndexRangeScan_38 |.| 1 | cop[tikv] | table:c, index:name(name) | tikv_task:{time:0s, loops:1}, scan_detail: {...} |.| N/A | N/A | | │ └─TableReader_37(Probe) |.| 10 | root | | time:543.7µs, loops:2, cop_task: {...} |.| 577 Bytes | N/A | | │ └─TableFullScan_36 |.| 10 | cop[tikv] | table:p | tikv_task:{time:0s, loops:1}, scan_detail: {...} |.| N/A | N/A | | └─TableReader_22(Probe) |.| 28 | root | | time:671.7µs, loops:2, cop_task: {...} |.| 876 Bytes | N/A | | └─TableFullScan_21 |.| 28 | cop[tikv] | table:m | tikv_task:{time:0s, loops:1}, scan_detail: {...} |.| N/A | N/A | +-----------------------------------------+.+---------+-----------+---------------------------+----------------------------------------------------------------+.+-----------+---------+ ``` ------------------------------------------------------------------------------ In addition to `EXPLAIN`, there's another method for examining execution plans called `EXPLAIN ANALYZE`. Unlike `EXPLAIN`, `EXPLAIN ANALYZE` not only generates the execution plan but also executes the SQL statement, captures runtime statistics, and presents both the statistics and the execution plan. `EXPLAIN ANALYZE` can be seen as an enhancement of `EXPLAIN`, providing more detailed information, such as: - The `actRows` column, which indicates the actual number of rows output by the operator. A significant discrepancy between `actRows` and `estRows` can suggest issues with the statistics, potentially requiring you to re-gather the statistics. - The `execution info` column, which includes actual execution details of the operator. For instance, `time` reflects the elapsed time for the operator, and `loops` represents the number of times the operator is invoked by its parent operator. Other attributes will be covered in future chapters. - The `memory` column, which shows the memory used by the operator in that particular step. - The `disk` column, which illustrates the disk space used by the operator in that particular step. The example query attempts to determine the average number of moons for planets categorized as Jovian. ``` tidb:db1> SELECT sum(pm.m_count)/count(*) FROM -> (SELECT count(m.name) m_count -> FROM universe.moons m -> RIGHT JOIN -> (SELECT p.id, p.name -> FROM universe.planet_categories c -> JOIN universe.planets p -> ON c.id = p.category_id AND c.name = 'Jovian') pc -> ON m.planet_id = pc.id -> GROUP BY pc.name) pm; +--------------------------+ | sum(pm.m_count)/count(*) | +--------------------------+ | 6.2500 | +--------------------------+ 1 row in set (0.01 sec) ```