The output of EXPLAIN ANALYZE is not the same with the plan shown on Dashboard

[Yui-Song]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. Create a TiDB cluster with nightly image.
2. Load TPCDS data, sf=50
3. Run Query_63

2. What did you expect to see? (Required)

This is the output from mysql client

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1005
Server version: 5.7.25-TiDB-v5.3.0-alpha-1053-g6e70be97c TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@tpcds50>explain analyze
    -> select  /* plan test 4*/ *
    -> from (select i_manager_id
    ->              ,sum(ss_sales_price) sum_sales
    ->              ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
    ->       from item
    ->           ,store_sales
    ->           ,date_dim
    ->           ,store
    ->       where ss_item_sk = i_item_sk
    ->         and ss_sold_date_sk = d_date_sk
    ->         and ss_store_sk = s_store_sk
    ->         and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11)
    ->         and ((    i_category in ('Books','Children','Electronics')
    ->               and i_class in ('personal','portable','reference','self-help')
    ->               and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
    -> 
Display all 1641 possibilities? (y or n) 
    ->                   'exportiunivamalg #9','scholaramalgamalg #9'))
    ->            or(    i_category in ('Women','Music','Men')
    ->               and i_class in ('accessories','classical','fragrances','pants')
    ->               and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
    -> 
Display all 1641 possibilities? (y or n) 
    ->                  'importoamalg #1')))
    -> group by i_manager_id, d_moy) tmp1
    -> where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
    -> order by i_manager_id
    ->         ,avg_monthly_sales
    ->         ,sum_sales
    -> limit 100;
+--------------------------------------------------+-----------+---------+--------------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| id                                               | estRows   | actRows | task         | access object                                                  | execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | memory   | disk    |
+--------------------------------------------------+-----------+---------+--------------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| Projection_24                                    | 0.80      | 100     | root         |                                                                | time:1m9s, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              | tpcds50.item.i_manager_id, Column#104, Column#106                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           | 9.44 KB  | N/A     |
| └─TopN_27                                        | 0.80      | 100     | root         |                                                                | time:1m9s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | tpcds50.item.i_manager_id, Column#106, Column#104, offset:0, count:100                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | 10.2 KB  | N/A     |
|   └─Selection_32                                 | 0.80      | 266     | root         |                                                                | time:1m9s, loops:3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | gt(case(gt(Column#106, 0), div(abs(minus(Column#104, Column#106)), Column#106), NULL), 0.1)                                                                                                                                                                                                                                                                                                                                                                                                                                                 | 28.1 KB  | N/A     |
|     └─Window_33                                  | 1.00      | 276     | root         |                                                                | time:1m9s, loops:3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | avg(Column#104)->Column#106 over(partition by tpcds50.item.i_manager_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A      | N/A     |
|       └─Sort_150                                 | 1.00      | 276     | root         |                                                                | time:1m9s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               | tpcds50.item.i_manager_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | 16.1 KB  | 0 Bytes |
|         └─HashAgg_34                             | 1.00      | 276     | root         |                                                                | time:1m9s, loops:6, partial_worker:{wall_time:1m8.98892105s, concurrency:5, task_num:25, tot_wait:5m44.93899803s, tot_exec:4.907913ms, tot_time:5m44.944109003s, max:1m8.988882745s, p95:1m8.988882745s}, final_worker:{wall_time:1m8.989111313s, concurrency:5, task_num:25, tot_wait:5m44.944067601s, tot_exec:1.199022ms, tot_time:5m44.945273426s, max:1m8.989081408s, p95:1m8.989081408s}                                                                                                                                                   | group by:tpcds50.date_dim.d_moy, tpcds50.item.i_manager_id, funcs:sum(tpcds50.store_sales.ss_sales_price)->Column#104, funcs:firstrow(tpcds50.item.i_manager_id)->tpcds50.item.i_manager_id                                                                                                                                                                                                                                                                                                                                                 | 951.8 KB | N/A     |
|           └─HashJoin_49                          | 48381.05  | 24240   | root         |                                                                | time:1m9s, loops:26, build_hash_table:{total:5.36ms, fetch:5.3ms, build:67.9µs}, probe:{concurrency:5, total:5m44.9s, max:1m9s, probe:15.3ms, fetch:5m44.9s}                                                                                                                                                                                                                                                                                                                                                                                     | inner join, equal:[eq(tpcds50.store_sales.ss_store_sk, tpcds50.store.s_store_sk)]                                                                                                                                                                                                                                                                                                                                                                                                                                                           | 8.23 KB  | 0 Bytes |
|             ├─TableReader_149(Build)             | 144.00    | 144     | root         |                                                                | time:5.26ms, loops:2, cop_task: {num: 1, max: 5.43ms, proc_keys: 0, rpc_num: 1, rpc_time: 5.42ms, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                                                                                                                                    | data:TableFullScan_148                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | 1.30 KB  | N/A     |
|             │ └─TableFullScan_148                | 144.00    | 144     | cop[tiflash] | table:store                                                    | tiflash_task:{time:4.4ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | N/A      | N/A     |
|             └─HashJoin_80(Probe)                 | 48381.05  | 24240   | root         |                                                                | time:1m9s, loops:26, build_hash_table:{total:12.2ms, fetch:12.1ms, build:108.6µs}, probe:{concurrency:5, total:5m44.9s, max:1m9s, probe:520.5ms, fetch:5m44.4s}                                                                                                                                                                                                                                                                                                                                                                                  | inner join, equal:[eq(tpcds50.store_sales.ss_sold_date_sk, tpcds50.date_dim.d_date_sk)]                                                                                                                                                                                                                                                                                                                                                                                                                                                     | 24.7 KB  | 0 Bytes |
|               ├─TableReader_145(Build)           | 364.00    | 365     | root         |                                                                | time:12ms, loops:2, cop_task: {num: 1, max: 12.3ms, proc_keys: 0, rpc_num: 1, rpc_time: 12.3ms, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                                                                                                                                      | data:Selection_144                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | 8.78 KB  | N/A     |
|               │ └─Selection_144                  | 364.00    | 365     | cop[tiflash] |                                                                | tiflash_task:{time:10.7ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | in(tpcds50.date_dim.d_month_seq, 1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 1222, 1223)                                                                                                                                                                                                                                                                                                                                                                                                                                    | N/A      | N/A     |
|               │   └─TableFullScan_143            | 73049.00  | 73049   | cop[tiflash] | table:date_dim                                                 | tiflash_task:{time:10.7ms, loops:2, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | N/A      | N/A     |
|               └─IndexHashJoin_94(Probe)          | 221084.16 | 5684892 | root         |                                                                | time:1m9s, loops:5555, inner:{total:3m42.7s, concurrency:5, task:4, construct:2ms, fetch:3m39.7s, build:639.6µs, join:3.04s}                                                                                                                                                                                                                                                                                                                                                                                                                     | inner join, inner:IndexLookUp_91, outer key:tpcds50.item.i_item_sk, inner key:tpcds50.store_sales.ss_item_sk, equal cond:eq(tpcds50.item.i_item_sk, tpcds50.store_sales.ss_item_sk)                                                                                                                                                                                                                                                                                                                                                         | 372.9 MB | N/A     |
|                 ├─TableReader_133(Build)         | 95.80     | 3240    | root         |                                                                | time:32.6ms, loops:6, cop_task: {num: 1, max: 32.7ms, proc_keys: 0, rpc_num: 1, rpc_time: 32.7ms, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                                                                                                                                    | data:Selection_132                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          | 219.6 KB | N/A     |
|                 │ └─Selection_132                | 95.80     | 3240    | cop[tiflash] |                                                                | tiflash_task:{time:28.8ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | or(and(in(tpcds50.item.i_category, "Books", "Children", "Electronics"), and(in(tpcds50.item.i_class, "personal", "portable", "reference", "self-help"), in(tpcds50.item.i_brand, "scholaramalgamalg #14", "scholaramalgamalg #7", "exportiunivamalg #9", "scholaramalgamalg #9"))), and(in(tpcds50.item.i_category, "Women", "Music", "Men"), and(in(tpcds50.item.i_class, "accessories", "classical", "fragrances", "pants"), in(tpcds50.item.i_brand, "amalgimporto #1", "edu packscholar #1", "exportiimporto #1", "importoamalg #1")))) | N/A      | N/A     |
|                 │   └─TableFullScan_131          | 62000.00  | 62000   | cop[tiflash] | table:item                                                     | tiflash_task:{time:15.8ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | N/A      | N/A     |
|                 └─IndexLookUp_91(Probe)          | 2307.77   | 5684892 | root         |                                                                | time:3m39.7s, loops:5558, index_task: {total_time: 3m28.8s, fetch_handle: 474.2ms, build: 464.2µs, wait: 3m28.3s}, table_task: {total_time: 17m57.7s, num: 313, concurrency: 20}                                                                                                                                                                                                                                                                                                                                                                 |                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             | 12.6 MB  | N/A     |
|                   ├─IndexRangeScan_88(Build)     | 2529.25   | 6096870 | cop[tikv]    | table:store_sales, index:PRIMARY(ss_item_sk, ss_ticket_number) | time:15.4ms, loops:6037, cop_task: {num: 150, max: 1.63s, min: 396µs, avg: 241.6ms, p95: 658.9ms, max_proc_keys: 88340, p95_proc_keys: 55671, tot_proc: 14.9s, tot_wait: 21.2s, rpc_num: 150, rpc_time: 36.2s, copr_cache_hit_ratio: 0.61}, tikv_task:{proc max:497ms, min:12ms, p80:207ms, p95:286ms, iters:6631, tasks:150}, scan_detail: {total_process_keys: 2219356, total_keys: 2220531, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2219356, block: {cache_hit_count: 1123, read_count: 2862, read_byte: 32.4 MB}}}             | range: decided by [eq(tpcds50.store_sales.ss_item_sk, tpcds50.item.i_item_sk)], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                            | N/A      | N/A     |
|                   └─Selection_90(Probe)          | 2307.77   | 5684892 | cop[tikv]    |                                                                | time:17m52.6s, loops:5983, cop_task: {num: 85524, max: 1.05s, min: 248.3µs, avg: 183.7ms, p95: 357ms, max_proc_keys: 117, p95_proc_keys: 90, tot_proc: 54m36s, tot_wait: 3h26m25.5s, rpc_num: 85524, rpc_time: 4h21m49s, copr_cache_hit_ratio: 0.16}, tikv_task:{proc max:111ms, min:0s, p80:19ms, p95:37ms, iters:168217, tasks:85524}, scan_detail: {total_process_keys: 5097798, total_keys: 5097866, rocksdb: {delete_skipped_count: 0, key_skipped_count: 136, block: {cache_hit_count: 9774402, read_count: 382548, read_byte: 6.16 GB}}}  | not(isnull(tpcds50.store_sales.ss_sold_date_sk)), not(isnull(tpcds50.store_sales.ss_store_sk))                                                                                                                                                                                                                                                                                                                                                                                                                                              | N/A      | N/A     |
|                     └─TableRowIDScan_89          | 2529.25   | 6096870 | cop[tikv]    | table:store_sales                                              | tikv_task:{proc max:111ms, min:0s, p80:19ms, p95:37ms, iters:168217, tasks:85524}                                                                                                                                                                                                                                                                                                                                                                                                                                                                | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | N/A      | N/A     |
+--------------------------------------------------+-----------+---------+--------------+----------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
21 rows in set (1 min 10.70 sec)

3. What did you see instead (Required)

This is the output of TiDB Dashboard.

explain analyze select   * from (select i_manager_id              ,sum(ss_sales_price) sum_sales              ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales       from item           ,store_sales           ,date_dim           ,store       where ss_item_sk = i_item_sk         and ss_sold_date_sk = d_date_sk         and ss_store_sk = s_store_sk         and d_month_seq in (1212,1212+1,1212+2,1212+3,1212+4,1212+5,1212+6,1212+7,1212+8,1212+9,1212+10,1212+11)         and ((    i_category in ('Books','Children','Electronics')               and i_class in ('personal','portable','reference','self-help')               and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',                   'exportiunivamalg #9','scholaramalgamalg #9'))            or(    i_category in ('Women','Music','Men')               and i_class in ('accessories','classical','fragrances','pants')               and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',                  'importoamalg #1'))) group by i_manager_id, d_moy) tmp1 where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1 order by i_manager_id         ,avg_monthly_sales         ,sum_sales limit 100;

id                                     	task        	estRows  	operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	memory  	disk
	Projection_24                          	root        	0.80     	tpcds50.item.i_manager_id, Column#104, Column#106                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          	100    	time:1m9s, loops:2, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	9.44 KB 	N/A
	└─TopN_27                              	root        	0.80     	tpcds50.item.i_manager_id, Column#106, Column#104, offset:0, count:100                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	100    	time:1m9s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	10.2 KB 	N/A
	  └─Selection_32                       	root        	0.80     	gt(case(gt(Column#106, 0), div(abs(minus(Column#104, Column#106)), Column#106), NULL), 0.1)                                                                                                                                                                                                                                                                                                                                                                                                                                                	266    	time:1m9s, loops:3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	28.1 KB 	N/A
	    └─Window_33                        	root        	1        	avg(Column#104)->Column#106 over(partition by tpcds50.item.i_manager_id)                                                                                                                                                                                                                                                                                                                                                                                                                                                                   	276    	time:1m9s, loops:3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	N/A     	N/A
	      └─Sort_150                       	root        	1        	tpcds50.item.i_manager_id                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  	276    	time:1m9s, loops:2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 	16.1 KB 	0 Bytes
	        └─HashAgg_34                   	root        	1        	group by:tpcds50.date_dim.d_moy, tpcds50.item.i_manager_id, funcs:sum(tpcds50.store_sales.ss_sales_price)->Column#104, funcs:firstrow(tpcds50.item.i_manager_id)->tpcds50.item.i_manager_id                                                                                                                                                                                                                                                                                                                                                	276    	time:1m9s, loops:6, partial_worker:{wall_time:1m8.98892105s, concurrency:5, task_num:25, tot_wait:5m44.93899803s, tot_exec:4.907913ms, tot_time:5m44.944109003s, max:1m8.988882745s, p95:1m8.988882745s}, final_worker:{wall_time:1m8.989111313s, concurrency:5, task_num:25, tot_wait:5m44.944067601s, tot_exec:1.199022ms, tot_time:5m44.945273426s, max:1m8.989081408s, p95:1m8.989081408s}                                                                                                                                                     	951.8 KB	N/A
	          └─HashJoin_49                	root        	48381.05 	inner join, equal:[eq(tpcds50.store_sales.ss_store_sk, tpcds50.store.s_store_sk)]                                                                                                                                                                                                                                                                                                                                                                                                                                                          	24240  	time:1m9s, loops:26, build_hash_table:{total:5.36ms, fetch:5.3ms, build:67.9µs}, probe:{concurrency:5, total:5m44.9s, max:1m9s, probe:15.3ms, fetch:5m44.9s}                                                                                                                                                                                                                                                                                                                                                                                      	8.23 KB 	0 Bytes
	            ├─HashJoin_80              	root        	48381.05 	inner join, equal:[eq(tpcds50.store_sales.ss_sold_date_sk, tpcds50.date_dim.d_date_sk)]                                                                                                                                                                                                                                                                                                                                                                                                                                                    	24240  	time:1m9s, loops:26, build_hash_table:{total:12.2ms, fetch:12.1ms, build:108.6µs}, probe:{concurrency:5, total:5m44.9s, max:1m9s, probe:520.5ms, fetch:5m44.4s}                                                                                                                                                                                                                                                                                                                                                                                   	24.7 KB 	0 Bytes
	            │ ├─IndexHashJoin_94       	root        	221084.16	inner join, inner:IndexLookUp_91, outer key:tpcds50.item.i_item_sk, inner key:tpcds50.store_sales.ss_item_sk, equal cond:eq(tpcds50.item.i_item_sk, tpcds50.store_sales.ss_item_sk)                                                                                                                                                                                                                                                                                                                                                        	5684892	time:1m9s, loops:5555, inner:{total:3m42.7s, concurrency:5, task:4, construct:2ms, fetch:3m39.7s, build:639.6µs, join:3.04s}                                                                                                                                                                                                                                                                                                                                                                                                                      	372.9 MB	N/A
	            │ │ ├─TableReader_133      	root        	95.80    	data:Selection_132                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         	3240   	time:32.6ms, loops:6, cop_task: {num: 1, max: 32.7ms, proc_keys: 0, rpc_num: 1, rpc_time: 32.7ms, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                                                                                                                                      	219.6 KB	N/A
	            │ │ │ └─Selection_132      	cop[tiflash]	95.80    	or(and(in(tpcds50.item.i_category, "Books", "Children", "Electronics"), and(in(tpcds50.item.i_class, "personal", "portable", "reference", "self-help"), in(tpcds50.item.i_brand, "scholaramalgamalg #14", "scholaramalgamalg #7", "exportiunivamalg #9", "scholaramalgamalg #9"))), and(in(tpcds50.item.i_category, "Women", "Music", "Men"), and(in(tpcds50.item.i_class, "accessories", "classical", "fragrances", "pants"), in(tpcds50.item.i_brand, "amalgimporto #1", "edu packscholar #1", "exportiimporto #1", "importoamalg #1"))))	3240   	tiflash_task:{time:28.8ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	N/A     	N/A
	            │ │ │   └─TableFullScan_131	cop[tiflash]	62000    	table:item, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               	62000  	tiflash_task:{time:15.8ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	N/A     	N/A
	            │ │ └─IndexLookUp_91       	root        	2307.77  	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	5684892	time:3m39.7s, loops:5558, index_task: {total_time: 3m28.8s, fetch_handle: 474.2ms, build: 464.2µs, wait: 3m28.3s}, table_task: {total_time: 17m57.7s, num: 313, concurrency: 20}                                                                                                                                                                                                                                                                                                                                                                  	12.6 MB 	N/A
	            │ │   ├─IndexRangeScan_88  	cop[tikv]   	2529.25  	table:store_sales, index:PRIMARY(ss_item_sk, ss_ticket_number), range: decided by [eq(tpcds50.store_sales.ss_item_sk, tpcds50.item.i_item_sk)], keep order:false                                                                                                                                                                                                                                                                                                                                                                           	6096870	time:15.4ms, loops:6037, cop_task: {num: 150, max: 1.63s, min: 396µs, avg: 241.6ms, p95: 658.9ms, max_proc_keys: 88340, p95_proc_keys: 55671, tot_proc: 14.9s, tot_wait: 21.2s, rpc_num: 291, rpc_time: 1m12.3s, copr_cache_hit_ratio: 0.61}, tikv_task:{proc max:497ms, min:12ms, p80:207ms, p95:286ms, iters:6631, tasks:150}, scan_detail: {total_process_keys: 2219356, total_keys: 2220531, rocksdb: {delete_skipped_count: 0, key_skipped_count: 2219356, block: {cache_hit_count: 1123, read_count: 2862, read_byte: 32.4 MB}}}            	N/A     	N/A
	            │ │   └─Selection_90       	cop[tikv]   	2307.77  	not(isnull(tpcds50.store_sales.ss_sold_date_sk)), not(isnull(tpcds50.store_sales.ss_store_sk))                                                                                                                                                                                                                                                                                                                                                                                                                                             	5684892	time:17m52.6s, loops:5983, cop_task: {num: 85524, max: 1.05s, min: 248.3µs, avg: 183.7ms, p95: 357ms, max_proc_keys: 117, p95_proc_keys: 90, tot_proc: 54m36s, tot_wait: 3h26m25.5s, rpc_num: 170781, rpc_time: 8h42m57.5s, copr_cache_hit_ratio: 0.16}, tikv_task:{proc max:111ms, min:0s, p80:19ms, p95:37ms, iters:168217, tasks:85524}, scan_detail: {total_process_keys: 5097798, total_keys: 5097866, rocksdb: {delete_skipped_count: 0, key_skipped_count: 136, block: {cache_hit_count: 9774402, read_count: 382548, read_byte: 6.16 GB}}}	N/A     	N/A
	            │ │     └─TableRowIDScan_89	cop[tikv]   	2529.25  	table:store_sales, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        	6096870	tikv_task:{proc max:111ms, min:0s, p80:19ms, p95:37ms, iters:168217, tasks:85524}                                                                                                                                                                                                                                                                                                                                                                                                                                                                  	N/A     	N/A
	            │ └─TableReader_145        	root        	364.00   	data:Selection_144                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         	365    	time:12ms, loops:2, cop_task: {num: 1, max: 12.3ms, proc_keys: 0, rpc_num: 1, rpc_time: 12.3ms, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                                                                                                                                        	8.78 KB 	N/A
	            │   └─Selection_144        	cop[tiflash]	364.00   	in(tpcds50.date_dim.d_month_seq, 1212, 1213, 1214, 1215, 1216, 1217, 1218, 1219, 1220, 1221, 1222, 1223)                                                                                                                                                                                                                                                                                                                                                                                                                                   	365    	tiflash_task:{time:10.7ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	N/A     	N/A
	            │     └─TableFullScan_143  	cop[tiflash]	73049    	table:date_dim, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           	73049  	tiflash_task:{time:10.7ms, loops:2, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	N/A     	N/A
	            └─TableReader_149          	root        	144      	data:TableFullScan_148                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	144    	time:5.26ms, loops:2, cop_task: {num: 1, max: 5.43ms, proc_keys: 0, rpc_num: 1, rpc_time: 5.42ms, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                                                                                                                                      	1.30 KB 	N/A
	              └─TableFullScan_148      	cop[tiflash]	144      	table:store, keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              	144    	tiflash_task:{time:4.4ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      	N/A     	N/A

4. What is your TiDB version? (Required)

Release Version: v5.3.0-alpha-1053-g6e70be97c
Edition: Community
Git Commit Hash: 6e70be97c2e5cc3b636ed9f55cfe7e73dd309138
Git Branch: master
UTC Build Time: 2021-10-09 13:56:08
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

[time-and-fate]

/assign chrysan

[chrysan]

Plan on Dashboard comes from tidb statements_summary. The above 2 plans should be the same but the plan of dashboard/statements_summary lacks comment/hint and probe/build tag. Will take it as an enhancement to summary plan.

[time-and-fate]

Seems the root cause is that the EXPLAIN and slow log plan are using two different implementations.

It will be better if we unify them.

But their logics are very different:

EXPLAIN:

• entry: (*Explain).RenderResult()
• Need to consider different explain options, some of which have very different outputs.
• Need to format the result here.
• Append the result strings to the result set of the EXPLAIN statement column by column and row by row.

Slow log:

• entry: getPlanTree()
• No formatting. Just output the necessary information and compress the result to minimize the size.
• Formatting is done in tidb_decode_plan(), which we don't need to consider here.
• The results are put into a single string.

I think a possible and reasonable way to refactor them is to

1. Implement a method for a plan tree that returns all information it can provide.
2. This information is stored in a slice, each element storing information of an operator, and ordered by the order of iterating the plan tree.
3. In EXPLAIN and slow log logic, collect and format the result from the slice.