How to decode a SQL plan

Problem Summary: Reverse engineer from a encoded SQL plan to a human-readable SQL

Solution/Next step: 

If you are inspecting your slow queries log, you might find entries like that:

# Time: 2021-03-31T10:59:35.039509349Z
# Txn_start_ts: 0
# User@Host: root[root] @ 127.0.0.1 [127.0.0.1]
# Conn_ID: 49795
# Query_time: 5.001415889
# Parse_time: 0.000085483
# Compile_time: 0.000248031
# Rewrite_time: 0.000123864
# Is_internal: false
# Digest: b4dae6a771c1d84157dcc302bef38cbff77a7a8ff89ee38302ac3324485454a3
# Num_cop_tasks: 0
# Prepared: false
# Plan_from_cache: false
# Has_more_results: false
# KV_total: 0
# PD_total: 0.000016877
# Backoff_total: 0
# Write_sql_response_total: 0.000004806
# Succ: true
# Plan: tidb_decode_plan('igHwiTAJM18zCTAJMQlzbGVlcCg1KS0+Q29sdW1uIzEJMQl0aW1lOjUuMDAwNzU5NDYzcywgbG9vcHM6MiwgQ29uY3VycmVuY3k6T0ZGCTAgQnl0ZXMJTi9BCjEJMjVfNAkwCTEJcm93czoxCTEJdGltZToyLjI5NsK1cywgbG9vcHM6MglOL0EJTi9BCg==')
# Plan_digest: a55eaee1d30304ad05500afbbfff1409ce22376e0f062c0d06ff34b8ed9c2b47
select sleep(5);

The Plan block shows you the SQL plan encoded :

Plan: tidb_decode_plan('igHwiTAJM18zCTAJMQlzbGVlcCg1KS0+Q29sdW1uIzEJMQl0aW1lOjUuMDAwNzU5NDYzcywgbG9vcHM6MiwgQ29uY3VycmVuY3k6T0ZGCTAgQnl0ZXMJTi9BCjEJMjVfNAkwCTEJcm93czoxCTEJdGltZToyLjI5NsK1cywgbG9vcHM6MglOL0EJTi9BCg==')

 You can decode to a human-readable plan by connecting to TiDB with the mysql client and issue:

select tidb_decode_plan('igHwiTAJM18zCTAJMQlzbGVlcCg1KS0+Q29sdW1uIzEJMQl0aW1lOjUuMDAwNzU5NDYzcywgbG9vcHM6MiwgQ29uY3VycmVuY3k6T0ZGCTAgQnl0ZXMJTi9BCjEJMjVfNAkwCTEJcm93czoxCTEJdGltZToyLjI5NsK1cywgbG9vcHM6MglOL0EJTi9BCg==');

And get back:

mysql> select tidb_decode_plan('igHwiTAJM18zCTAJMQlzbGVlcCg1KS0+Q29sdW1uIzEJMQl0aW1lOjUuMDAwNzU5NDYzcywgbG9vcHM6MiwgQ29uY3VycmVuY3k6T0ZGCTAgQnl0ZXMJTi9BCjEJMjVfNAkwCTEJcm93czoxCTEJdGltZToyLjI5NsK1cywgbG9vcHM6MglOL0EJTi9BCg==');
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_decode_plan('igHwiTAJM18zCTAJMQlzbGVlcCg1KS0+Q29sdW1uIzEJMQl0aW1lOjUuMDAwNzU5NDYzcywgbG9vcHM6MiwgQ29uY3VycmVuY3k6T0ZGCTAgQnl0ZXMJTi9BCjEJMjVfNAkwCTEJcm93czoxCTEJdGltZToyLjI5NsK1cywgbG9vcHM6MglOL0EJTi9BCg==') |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id task estRows operator info actRows execution info memory disk
Projection_3 root 1 sleep(5)->Column#1 1 time:5.000759463s, loops:2, Concurrency:OFF 0 Bytes N/A
└─TableDual_4 root 1 rows:1 1 time:2.296µs, loops:2 N/A N/A |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)