Performance Differences of the Same SQL in Versions 3.0.5 and 5.3.0

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

Original topic: 同一个sql在3.0.5与5.3.0版本性能差别

| username: beebol

[TiDB Usage Environment] Production Environment
[TiDB Version]
New Version 5.3.0
Old Version 3.0.5

The execution result in the old version is as follows:

explain select o.account_id , sum(o.amount),sum(if(c.open_type = 1,o.amount,0)),sum(if(c.open_type = 1,1,0)),sum(if(c.open_type != 1,o.amount,0)),sum(if(c.open_type != 1,1,0))  
    ->                         from new_boss.`order` o left join new_boss.invoice i on o.uuid = i.order_uuid left join product p on i.product_id = p.id  
    ->                          left join channel c on c.id = o.channel_id  where o.account_id = 164121770 and i.end_time >=  '2023-05-18' and  i.delivered_at > '2023-05-10 15:05:45' and  i.delivered_at < '2023-05-24'  and p.cate =1 and i.status =1 and c.open_type in (1,2,3) ;
+--------------------------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | count | task | operator info                                                                                                                                                                                                                                      |
+--------------------------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_11                        | 1.00  | root | new_boss.o.account_id, 9_col_0, 9_col_1, 9_col_2, 9_col_3, 9_col_4                                                                                                                                                                                 |
| └─StreamAgg_16                       | 1.00  | root | funcs:sum(col_0), sum(col_1), sum(col_2), sum(col_3), sum(col_4), firstrow(col_5)                                                                                                                                                                  |
|   └─Projection_126                   | 0.00  | root | cast(new_boss.o.amount), cast(if(eq(boss.c.open_type, 1), new_boss.o.amount, 0)), cast(if(eq(boss.c.open_type, 1), 1, 0)), cast(if(ne(boss.c.open_type, 1), new_boss.o.amount, 0)), cast(if(ne(boss.c.open_type, 1), 1, 0)), new_boss.o.account_id |
|     └─IndexJoin_21                   | 0.00  | root | inner join, inner:TableReader_20, outer key:new_boss.o.channel_id, inner key:boss.c.id                                                                                                                                                             |
|       ├─IndexJoin_51                 | 0.00  | root | inner join, inner:TableReader_50, outer key:new_boss.i.product_id, inner key:boss.p.id                                                                                                                                                             |
|       │ ├─IndexJoin_77               | 0.00  | root | inner join, inner:IndexLookUp_76, outer key:new_boss.o.uuid, inner key:new_boss.i.order_uuid                                                                                                                                                       |
|       │ │ ├─IndexLookUp_100          | 0.00  | root |                                                                                                                                                                                                                                                    |
|       │ │ │ ├─IndexScan_98           | 0.00  | cop  | table:o, index:account_id, range:[164121770,164121770], keep order:false                                                                                                                                                                           |
|       │ │ │ └─TableScan_99           | 0.00  | cop  | table:order, keep order:false                                                                                                                                                                                                                      |
|       │ │ └─IndexLookUp_76           | 0.00  | root |                                                                                                                                                                                                                                                    |
|       │ │   ├─IndexScan_73           | 1.00  | cop  | table:i, index:order_uuid, range: decided by [eq(new_boss.i.order_uuid, new_boss.o.uuid)], keep order:false                                                                                                                                        |
|       │ │   └─Selection_75           | 0.00  | cop  | eq(new_boss.i.status, 1), ge(new_boss.i.end_time, 2023-05-18 00:00:00.000000), gt(new_boss.i.delivered_at, 2023-05-10 15:05:45.000000), lt(new_boss.i.delivered_at, 2023-05-24 00:00:00.000000), not(isnull(new_boss.i.product_id))                |
|       │ │     └─TableScan_74         | 1.00  | cop  | table:invoice, keep order:false                                                                                                                                                                                                                    |
|       │ └─TableReader_50             | 0.80  | root | data:Selection_49                                                                                                                                                                                                                                  |
|       │   └─Selection_49             | 0.80  | cop  | eq(boss.p.cate, 1)                                                                                                                                                                                                                                 |
|       │     └─TableScan_48           | 1.00  | cop  | table:p, range: decided by [new_boss.i.product_id], keep order:false                                                                                                                                                                               |
|       └─TableReader_20               | 0.80  | root | data:Selection_19                                                                                                                                                                                                                                  |
|         └─Selection_19               | 0.80  | cop  | in(boss.c.open_type, 1, 2, 3)                                                                                                                                                                                                                      |
|           └─TableScan_18             | 1.00  | cop  | table:c, range: decided by [new_boss.o.channel_id], keep order:false, stats:pseudo                                                                                                                                                                 |
+--------------------------------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
19 rows in set (0.01 sec)

root 11:28:  [boss]> select o.account_id , sum(o.amount),sum(if(c.open_type = 1,o.amount,0)),sum(if(c.open_type = 1,1,0)),sum(if(c.open_type != 1,o.amount,0)),sum(if(c.open_type != 1,1,0))  
    ->                         from new_boss.`order` o left join new_boss.invoice i on o.uuid = i.order_uuid left join product p on i.product_id = p.id  
    ->                          left join channel c on c.id = o.channel_id  where o.account_id = 164121770 and i.end_time >=  '2023-05-18' and  i.delivered_at > '2023-05-10 15:05:45' and  i.delivered_at < '2023-05-24'  and p.cate =1 and i.status =1 and c.open_type in (1,2,3) ;
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
| account_id | sum(o.amount) | sum(if(c.open_type = 1,o.amount,0)) | sum(if(c.open_type = 1,1,0)) | sum(if(c.open_type != 1,o.amount,0)) | sum(if(c.open_type != 1,1,0)) |
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
|       NULL |          NULL |                                NULL |                         NULL |                                 NULL |                          NULL |
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
1 row in set (0.04 sec)

The execution time is relatively normal and fast.

The execution result in the new version is as follows:

explain select o.account_id , sum(o.amount),sum(if(c.open_type = 1,o.amount,0)),sum(if(c.open_type = 1,1,0)),sum(if(c.open_type != 1,o.amount,0)),sum(if(c.open_type != 1,1,0))  
    ->                         from new_boss.`order` o left join new_boss.invoice i on o.uuid = i.order_uuid left join product p on i.product_id = p.id  
    ->                          left join channel c on c.id = o.channel_id  where o.account_id = 164121770 and i.end_time >=  '2023-05-18' and  i.delivered_at > '2023-05-10 15:05:45' and  i.delivered_at < '2023-05-24'  and p.cate =1 and i.status =1 and c.open_type in (1,2,3) ;
+---------------------------------------------------+---------+--------------+-----------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                                | estRows | task         | access object                                 | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+---------------------------------------------------+---------+--------------+-----------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_17                                     | 1.00    | root         |                                               | new_boss.order.account_id, Column#130, Column#131, Column#132, Column#133, Column#134                                                                                                                                                                                                                                                                                                                                                                                |
| └─HashAgg_18                                      | 1.00    | root         |                                               | funcs:sum(Column#170)->Column#130, funcs:sum(Column#171)->Column#131, funcs:sum(Column#172)->Column#132, funcs:sum(Column#173)->Column#133, funcs:sum(Column#174)->Column#134, funcs:firstrow(Column#175)->new_boss.order.account_id                                                                                                                                                                                                                                 |
|   └─Projection_245                                | 489.00  | root         |                                               | cast(new_boss.order.amount, decimal(32,0) BINARY)->Column#170, cast(if(eq(boss.channel.open_type, 1), new_boss.order.amount, 0), decimal(32,0) BINARY)->Column#171, cast(if(eq(boss.channel.open_type, 1), 1, 0), decimal(22,0) BINARY)->Column#172, cast(if(ne(boss.channel.open_type, 1), new_boss.order.amount, 0), decimal(32,0) BINARY)->Column#173, cast(if(ne(boss.channel.open_type, 1), 1, 0), decimal(22,0) BINARY)->Column#174, new_boss.order.account_id |
|     └─IndexJoin_31                                | 489.00  | root         |                                               | inner join, inner:TableReader_27, outer key:new_boss.invoice.product_id, inner key:boss.product.id, equal cond:eq(new_boss.invoice.product_id, boss.product.id)                                                                                                                                                                                                                                                                                                      |
|       ├─HashJoin_106(Build)                       | 489.00  | root         |                                               | inner join, equal:[eq(new_boss.order.channel_id, boss.channel.id)]                                                                                                                                                                                                                                                                                                                                                                                                   |
|       │ ├─HashJoin_164(Build)                     | 489.00  | root         |                                               | inner join, equal:[eq(new_boss.order.uuid, new_boss.invoice.order_uuid)]                                                                                                                                                                                                                                                                                                                                                                                             |
|       │ │ ├─IndexLookUp_200(Build)                | 606.94  | root         |                                               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|       │ │ │ ├─IndexRangeScan_197(Build)           | 942.02  | cop[tikv]    | table:i, index:idx_delivered_at(delivered_at) | range:(2023-05-10 15:05:45,2023-05-24 00:00:00), keep order:false                                                                                                                                                                                                                                                                                                                                                                                                    |
|       │ │ │ └─Selection_199(Probe)                | 606.94  | cop[tikv]    |                                               | eq(new_boss.invoice.status, 1), ge(new_boss.invoice.end_time, 2023-05-18 00:00:00.000000), not(isnull(new_boss.invoice.product_id))                                                                                                                                                                                                                                                                                                                                  |
|       │ │ │   └─TableRowIDScan_198                | 942.02  | cop[tikv]    | table:i                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       │ │ └─IndexLookUp_186(Probe)                | 611.25  | root         |                                               |                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
|       │ │   ├─IndexRangeScan_184(Build)           | 611.25  | cop[tikv]    | table:o, index:idx_account_id(account_id)     | range:[164121770,164121770], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                        |
|       │ │   └─TableRowIDScan_185(Probe)           | 611.25  | cop[tikv]    | table:o                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       │ └─TableReader_212(Probe)                  | 645.00  | root         |                                               | data:Selection_211                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|       │   └─Selection_211                         | 645.00  | cop[tiflash] |                                               | in(boss.channel.open_type, 1, 2, 3)                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|       │     └─TableFullScan_210                   | 671.00  | cop[tiflash] | table:c                                       | keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
|       └─TableReader_27(Probe)                     | 0.32    | root         |                                               | data:Selection_26                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
|         └─Selection_26                            | 0.32    | cop[tikv]    |                                               | eq(boss.product.cate, 1)                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|           └─TableRangeScan_25                     | 1.00    | cop[tikv]    | table:p                                       | range: decided by [new_boss.invoice.product_id], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                    |
+---------------------------------------------------+---------+--------------+-----------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
19 rows in set (0.01 sec)

root 11:28:  [boss]> select o.account_id , sum(o.amount),sum(if(c.open_type = 1,o.amount,0)),sum(if(c.open_type = 1,1,0)),sum(if(c.open_type != 1,o.amount,0)),sum(if(c.open_type != 1,1,0))  
    ->                         from new_boss.`order` o left join new_boss.invoice i on o.uuid = i.order_uuid left join product p on i.product_id = p.id  
    ->                          left join channel c on c.id = o.channel_id  where o.account_id = 164121770 and i.end_time >=  '2023-05-18' and  i.delivered_at > '2023-05-10 15:05:45' and  i.delivered_at < '2023-05-24'  and p.cate =1 and i.status =1 and c.open_type in (1,2,3) ;
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
| account_id | sum(o.amount) | sum(if(c.open_type = 1,o.amount,0)) | sum(if(c.open_type = 1,1,0)) | sum(if(c.open_type != 1,o.amount,0)) | sum(if(c.open_type != 1,1,0)) |
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
|       NULL |          NULL |                                NULL |                         NULL |                                 NULL |                          NULL |
+------------+---------------+-------------------------------------+------------------------------+--------------------------------------+-------------------------------+
1 row in set (19.31 sec)

In the new version, there is a TiFlash component, and the entire execution takes nearly 20 seconds. The table structure and indexes are the same. Even if forced to use the old version’s index and turning off the MPP parameter, it is still very slow. How can this problem be solved? Are there any configuration parameters that have not been adjusted properly?

| username: zhanggame1 | Original post link

Is the data volume the same? Try creating a new table for analysis.

| username: xingzhenxiang | Original post link

show stats_healthy to check the health of the table

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

The main issue is with the new_boss.invoice table. There seems to be a problem with the index on the delivered_at field. Collect the statistics for this table first.

| username: beebol | Original post link

show stats_healthy;
| new_boss              | invoice                                                     |                |      97 |
| username: beebol | Original post link

The data volume is the same, it’s just the same table placed in different clusters.

| username: xingzhenxiang | Original post link

The health status is normal, and I don’t have any other ideas.

| username: WalterWj | Original post link

Try setting the engine directly to only use TiKV and TiDB.

| username: beebol | Original post link

The problem is almost found, but the cause is still unclear.

  1. After updating the statistics of table i, the execution plan used table o as the driving table (same as the old version), but table i still used the idx_delivered_at index. Normally, it should use the join index (idx_order_uuid). I don’t know why it didn’t use it and instead used the index in the where clause.
  2. After forcing the use of the idx_order_uuid index, the speed is the same as the old cluster.
| username: zhanggame1 | Original post link

The issue of execution plan selection is common to all databases. Just a few days ago, I encountered a single table query on Oracle19 that did not use the primary key index, even though the WHERE condition included the primary key column.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.