How TiDB Estimates the Cost of Each Operator in a Query Plan

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

Original topic: TiDB 如何知晓查询计划里每个算子预估的代价

| username: Wind-Gone222

To improve efficiency, please provide the following information. A clear problem description can lead to a quicker resolution:

[Overview] I want to know how to determine the estimated cost of each operator based on the cost model in the query plan.

[Application Framework and Development Adaptation Business Logic]

[Background] What operations have been performed

[Phenomenon] Business and database phenomena

[Problem] The current issue encountered

[Business Impact]

[TiDB Version] v7.5.1

[Attachments] Relevant logs and monitoring

| username: Wind-Gone222 | Original post link

Whether it is OB, PG, or other mainstream databases, they all provide such information. How can TiDB obtain it?

| username: hey-hoho | Original post link

You might be looking for this:

| username: DBAER | Original post link

I think it’s just trace.

| username: oceanzhang | Original post link

Definitely based on the planned statistics.

| username: 春风十里 | Original post link

The optimizer calculates the cost of each operator based on statistical information. Are you interested in learning about the optimizer?
Unveiling the New TiDB Optimizer: Cascades Planner Principle Analysis | PingCAP

| username: 裤衩儿飞上天 | Original post link

Are you asking to display the estcost of each operator?

| username: TiDBer_JUi6UvZm | Original post link

The general practice is to count the number of scanned rows, whether temporary tables are used, and whether sorting is required.

| username: TiDBer_JUi6UvZm | Original post link

However, each manufacturer has its own tweaks, so it’s hard to say.

| username: Wind-Gone222 | Original post link

Yes, I want to know the estcost.

| username: Wind-Gone222 | Original post link

Hello, thank you. This shows the time for each link. What I want to see is the estimated cost for each operator, similar to:

| username: 裤衩儿飞上天 | Original post link

When viewing the execution plan, adding format=verbose will display the estcost column.

| username: Wind-Gone222 | Original post link

That’s right, I want to understand, but I want to understand through testing, which I can’t get from the query plan.

| username: Wind-Gone222 | Original post link

I see! Thank you, thank you.

| username: Wind-Gone222 | Original post link

Hello, I would like to ask for additional information. So for the verbose format of TiDB, is the overall query cost just the sum of the costs of all operators?

| username: dba远航 | Original post link

COST means expenditure.

| username: TiDBer_RjzUpGDL | Original post link

Learned.

| username: 人如其名 | Original post link

mysql> show variables like '%cost_model%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| tidb_cost_model_version | 2     |
+-------------------------+-------+
1 row in set (0.00 sec)

In the case of tidb_cost_model_version = 2, you can check the cost calculation method of the execution plan:

mysql> explain analyze format=true_card_cost select * from customer where C_ADDRESS='abc' order by C_PHONE limit 100;
+------------------------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------+-----------+------+
| id                           | estRows   | estCost     | costFormula                                                                                                                                                                                                                                                                                                                                                                                                                                | actRows | task      | access object  | execution info                                                                                                                                                                                                                       | operator info                              | memory    | disk |
+------------------------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------+-----------+------+
| TopN_8                       | 1.43      | 3728257.14  | (((((cpu(150000*filters(1)*tikv_cpu_factor(49.9))) + (scan(150000*logrowsize(242.69)*tikv_scan_factor(40.7)))) + ((exprCPU(0*0*tikv_cpu_factor(49.9))) + (orderCPU(0*log(100)*tikv_cpu_factor(49.9)))) + (topMem(100*216*tikv_mem_factor(0.2)))) + (net(0*rowsize(216)*tidb_kv_net_factor(3.96))))/15.00) + ((exprCPU(0*0*tidb_cpu_factor(49.9))) + (orderCPU(0*log(100)*tidb_cpu_factor(49.9)))) + (topMem(100*216*tidb_mem_factor(0.2))) | 0       | root      |                | time:345.2ms, loops:1                                                                                                                                                                                                                | tpch.customer.c_phone, offset:0, count:100 | 0 Bytes   | N/A  |
| └─TableReader_16             | 1.43      | 3723937.14  | ((((cpu(150000*filters(1)*tikv_cpu_factor(49.9))) + (scan(150000*logrowsize(242.69)*tikv_scan_factor(40.7)))) + ((exprCPU(0*0*tikv_cpu_factor(49.9))) + (orderCPU(0*log(100)*tikv_cpu_factor(49.9)))) + (topMem(100*216*tikv_mem_factor(0.2)))) + (net(0*rowsize(216)*tidb_kv_net_factor(3.96))))/15.00                                                                                                                                    | 0       | root      |                | time:345.1ms, loops:2, cop_task: {num: 1, max: 344.8ms, proc_keys: 150000, tot_proc: 343ms, rpc_num: 1, rpc_time: 344.8ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                      | data:TopN_15                               | 277 Bytes | N/A  |
|   └─TopN_15                  | 1.43      | 55859057.06 | ((cpu(150000*filters(1)*tikv_cpu_factor(49.9))) + (scan(150000*logrowsize(242.69)*tikv_scan_factor(40.7)))) + ((exprCPU(0*0*tikv_cpu_factor(49.9))) + (orderCPU(0*log(100)*tikv_cpu_factor(49.9)))) + (topMem(100*216*tikv_mem_factor(0.2)))                                                                                                                                                                                               | 0       | cop[tikv] |                | tikv_task:{time:343ms, loops:147}, scan_detail: {total_process_keys: 150000, total_process_keys_size: 30533765, total_keys: 150001, get_snapshot_time: 87.9µs, rocksdb: {key_skipped_count: 150000, block: {cache_hit_count: 501}}}  | tpch.customer.c_phone, offset:0, count:100 | N/A       | N/A  |
|     └─Selection_14           | 1.43      | 55854737.06 | (cpu(150000*filters(1)*tikv_cpu_factor(49.9))) + (scan(150000*logrowsize(242.69)*tikv_scan_factor(40.7)))                                                                                                                                                                                                                                                                                                                                  | 0       | cop[tikv] |                | tikv_task:{time:343ms, loops:147}                                                                                                                                                                                                    | eq(tpch.customer.c_address, "abc")         | N/A       | N/A  |
|       └─TableFullScan_13     | 211936.00 | 48369737.06 | scan(150000*logrowsize(242.69)*tikv_scan_factor(40.7))                                                                                                                                                                                                                                                                                                                                                                                     | 150000  | cop[tikv] | table:customer | tikv_task:{time:338ms, loops:147}                                                                                                                                                                                                    | keep order:false                           | N/A       | N/A  |
+------------------------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------+-----------+------+
5 rows in set, 3 warnings (0.37 sec)

The total cost is not simply accumulated; corresponding processing will also be done in the case of parallelism.

| username: TIDB-Learner | Original post link

Isn’t it in the dashboard?

| username: shigp_TIDBER | Original post link

Cannot proceed without trace