TiDB Slow Query Execution Plan

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TiDB慢查询执行计划

| username: 超7成网友

There is a slow query, and I checked the execution plan on the dashboard:
[Total Execution Time] 324ms, apart from [Coprocessor Execution Time] which is over 50ms, there are no other obvious time-consuming parts. Generally, where is the time consumed in such cases?
The table index information is as follows:

  1. Primary key id, create_time; partitioned by create_time
  2. Secondary index cid
    Query statement: select * from table where cid = 123;

| username: xfworld | Original post link

Have the statistics not been updated? Why are they all zeros…

| username: tidb菜鸟一只 | Original post link

You can manually execute the SQL with EXPLAIN ANALYZE + SQL to check the execution plan. If the keyword “pseudo” appears, then analyze the table.

| username: 超7成网友 | Original post link

The return of this statement is empty; there are no statements that meet the conditions. Is this the reason?

| username: 超7成网友 | Original post link

There is no such pseudo

| username: tidb菜鸟一只 | Original post link

Could you post the execution plan to see where the slowdown is?

| username: xfworld | Original post link

No, even if the index is hit, row information still needs to be filtered.

| username: zhaokede | Original post link

You need to look at the specific execution plan;
With the current information, it’s still not clear.

| username: forever | Original post link

After hitting the index and filtering out no data, there’s no need to go back to the table to filter row information, right?

| username: DBAER | Original post link

How about tracing it?

| username: Soysauce520 | Original post link

Try using EXPLAIN ANALYZE on the client to check, and how are the CPU and memory resources of TiDB?

| username: h5n1 | Original post link

What is the configuration of the TiDB server? Is the CPU utilization high? Please copy and upload the text form of the SQL execution plan.

| username: QH琉璃 | Original post link

The experts have already provided various solutions.

| username: TiDBer_pakki | Original post link

Partition all, it should have scanned all partitions.

| username: 超7成网友 | Original post link

Thank you,

| id                             | estRows | estCost | actRows | task      | access object                                              | execution info                                                                                                                                                                                                                                                                                                                                                                 | operator info                                                                                                                                                                                                                                                                                   | memory    | disk  |
| Projection_6                   | 0.00    | 1969.62 | 0       | root      |                                                            | time:322.2ms, loops:1, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                         | o_dp.t_hr.business_id, o_dp.t_hr.output_amount, o_dp.t_hr.output_product, o_dp.t_hr.create_time, o_dp.t_hr.output_result                                                | 81.5 KB   | N/A   |
| └─Projection_13                | 0.00    | 1969.12 | 0       | root      |                                                            | time:322.2ms, loops:1, Concurrency:OFF                                                                                                                                                                                                                                                                                                                                         | o_dp.t_hr.business_id, o_dp.t_hr.customer_id, o_dp.t_hr.create_time, o_dp.t_hr.output_amount, o_dp.t_hr.output_result, o_dp.t_hr.output_product | 89.7 KB   | N/A   |
|   └─IndexLookUp_12             | 0.00    | 1968.52 | 0       | root      | partition:all                                              | time:322.2ms, loops:1                                                                                                                                                                                                                                                                                                                                                          |                                                                                                                                                                                                                                                                                                 | 233 Bytes | N/A   |
|     ├─IndexRangeScan_10(Build) | 0.00    | 234.84  | 0       | cop[tikv] | table:t_hr, index:idx_customer_id(customer_id) | time:51.7ms, loops:41, cop_task: {num: 41, max: 270.6ms, min: 545.5µs, avg: 7.61ms, p95: 1.74ms, rpc_num: 41, rpc_time: 309.6ms, copr_cache_hit_ratio: 0.02, distsql_concurrency: 15}, tikv_task:{proc max:3ms, min:0s, avg: 292.7µs, p80:1ms, p95:1ms, iters:41, tasks:41}, scan_detail: {total_keys: 40, get_snapshot_time: 917µs, rocksdb: {block: {cache_hit_count: 492}}} | range:["320282199601025723","320282199601025723"], keep order:false                                                                                                                                                                                                                             | N/A       | N/A   |
|     └─TableRowIDScan_11(Probe) | 0.00    | 530.55  | 0       | cop[tikv] | table:t_hr                                     |                                                                                                                                                                                                                                                                                                                                                                                | keep order:false                                                                                                                                                                                                                                                                                | N/A       | N/A   |
| username: h5n1 | Original post link

There seems to be an issue with the timing. There are 41 cop tasks, with the largest one taking 270.6ms, but the recorded time here is 51.7ms.

| username: TiDBer_aKu9dgpb | Original post link

First, check the server load. Generally, slow coroutine scheduling is related to IO or CPU pressure.

| username: 小于同学 | Original post link

No, even if the index is hit, row information still needs to be filtered.

| username: TiDBer_21wZg5fm | Original post link

The information provided is incomplete.

| username: TiDBer_小阿飞 | Original post link

There is no necessary connection between slow queries and indexes; the execution efficiency of an SQL statement ultimately depends on the number of rows scanned. Additionally, virtual columns and composite indexes can be used to improve the execution efficiency of complex queries.