More details about table scan in the explain analyze output

[JaySon-Huang]

Enhancement

Currently, the execution summaries shown in the explain analyze output miss some details information.

We can figure out whether we can return more information that can help us locating performance issues, such as:

• The cost of read index
• The cost of wait index
• The rough set filter storage layer used
• The filter rate of rought set filter

-- explain analyze output for tiflash
mysql> set tidb_isolation_read_engines = 'tiflash,tidb'; explain analyze select count(*) from history;
+--------------------------+--------------+-----------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+------+
| id                       | estRows      | actRows   | task         | access object | execution info                                                                                                                                                                                                                                                                                                                                                       | operator info                   | memory  | disk |
+--------------------------+--------------+-----------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+------+
| HashAgg_17               | 1.00         | 1         | root         |               | time:613.7ms, loops:2, partial_worker:{wall_time:613.39084ms, concurrency:5, task_num:1, tot_wait:3.066170582s, tot_exec:6.414µs, tot_time:3.066282161s, max:613.288928ms, p95:613.288928ms}, final_worker:{wall_time:613.549326ms, concurrency:5, task_num:1, tot_wait:3.066439488s, tot_exec:118.905µs, tot_time:3.067209872s, max:613.477518ms, p95:613.477518ms} | funcs:count(Column#7)->Column#5 | 9.86 KB | N/A  |
| └─TableReader_19         | 1.00         | 1         | root         |               | time:613.2ms, loops:2, cop_task: {num: 1, max: 0s, proc_keys: 0, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                         | data:ExchangeSender_18          | N/A     | N/A  |
|   └─ExchangeSender_18    | 1.00         | 1         | mpp[tiflash] |               | tiflash_task:{time:605.3ms, loops:1, threads:24}                                                                                                                                                                                                                                                                                                                     | ExchangeType: PassThrough       | N/A     | N/A  |
|     └─HashAgg_8          | 1.00         | 1         | mpp[tiflash] |               | tiflash_task:{time:605.3ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                      | funcs:count(1)->Column#7        | N/A     | N/A  |
|       └─TableFullScan_16 | 150426752.00 | 150368885 | mpp[tiflash] | table:history | tiflash_task:{time:562.3ms, loops:2348, threads:24}                                                                                                                                                                                                                                                                                                                  | keep order:false                | N/A     | N/A  |
+--------------------------+--------------+-----------+--------------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+---------+------+

-- explain analyze output for tikv
mysql> set tidb_isolation_read_engines = 'tikv,tidb'; explain analyze select count(*) from history;

+------------------------+--------------+-----------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-----------+------+
| id                     | estRows      | actRows   | task      | access object | execution info                                                                                                                                                                                                                                                                                                                                                             | operator info                   | memory    | disk |
+------------------------+--------------+-----------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-----------+------+
| StreamAgg_16           | 1.00         | 1         | root      |               | time:5.25s, loops:2                                                                                                                                                                                                                                                                                                                                                        | funcs:count(Column#7)->Column#5 | 2.16 KB   | N/A  |
| └─TableReader_17       | 1.00         | 192       | root      |               | time:5.25s, loops:2, cop_task: {num: 192, max: 802.3ms, min: 134.7ms, avg: 385.8ms, p95: 656.5ms, max_proc_keys: 1341440, p95_proc_keys: 1310720, tot_proc: 1m13.8s, tot_wait: 96ms, rpc_num: 192, rpc_time: 1m14.1s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                 | data:StreamAgg_8                | 354 Bytes | N/A  |
|   └─StreamAgg_8        | 1.00         | 192       | cop[tikv] |               | tikv_task:{proc max:758ms, min:133ms, avg: 383.1ms, p80:611ms, p95:655ms, iters:146969, tasks:192}, scan_detail: {total_process_keys: 150368885, total_process_keys_size: 4059959895, total_keys: 150580729, get_snapshot_time: 77.2ms, rocksdb: {key_skipped_count: 150580537, block: {cache_hit_count: 1297, read_count: 160495, read_byte: 1.94 GB, read_time: 1.54s}}} | funcs:count(1)->Column#7        | N/A       | N/A  |
|     └─TableFullScan_15 | 150426752.00 | 150368885 | cop[tikv] | table:history | tikv_task:{proc max:758ms, min:133ms, avg: 382.7ms, p80:609ms, p95:654ms, iters:146969, tasks:192}                                                                                                                                                                                                                                                                         | keep order:false                | N/A       | N/A  |
+------------------------+--------------+-----------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------+-----------+------+

Related method:

tiflash/dbms/src/Flash/Coprocessor/DAGResponseWriter.cpp

Lines 67 to 70 in 892f852

	void DAGResponseWriter::addExecuteSummaries(tipb::SelectResponse & response, bool delta_mode)
	{
	if (!dag_context.collect_execution_summaries)
	return;