分区裁剪不掉第一个分区

* 【TiDB 版本】:

3.0.11

* 【问题描述】:

下面的SQL语句 执行后 多扫描了一次 partition:p201701

EXPLAIN SELECT
   COUNT( * )
FROM
   dc_flowdata_deliver_sale_range sale
WHERE
   sale.seller_date BETWEEN '2020-03-01' AND '2020-03-31';
   
   
   
+----------------------+-------------+------+----------------------------------------------------------------------------------------------------------------------------+
| id                   | count       | task | operator info                                                                                                              |
+----------------------+-------------+------+----------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_13         | 1.00        | root | funcs:count(1)                                                                                                             |
| └─Union_21           | 83310.01    | root |                                                                                                                            |
|   ├─IndexReader_24   | 2113.01     | root | index:Selection_23                                                                                                         |
|   │ └─Selection_23   | 2113.01     | cop  | ge(prd2_pfizer.sale.seller_date, 2020-03-01 00:00:00.000000), le(prd2_pfizer.sale.seller_date, 2020-03-31 00:00:00.000000) |
|   │   └─IndexScan_22 | 12908885.00 | cop  | table:sale, partition:p201701, index:paas_id, seller_date, range:[NULL,+inf], keep order:false                             |
|   └─IndexReader_27   | 81197.00    | root | index:Selection_26                                                                                                         |
|     └─Selection_26   | 81197.00    | cop  | ge(prd2_pfizer.sale.seller_date, 2020-03-01 00:00:00.000000), le(prd2_pfizer.sale.seller_date, 2020-03-31 00:00:00.000000) |
|       └─IndexScan_25 | 81197.00    | cop  | table:sale, partition:p202003, index:paas_id, seller_date, range:[NULL,+inf], keep order:false                             |
+----------------------+-------------+------+----------------------------------------------------------------------------------------------------------------------------+

8 rows in set
Time: 0.026s


确保 p201701 中没有 2020-03 的数据


SELECT
   COUNT( * )
FROM
   dc_flowdata_deliver_sale_range PARTITION (p201701)
WHERE
   seller_date BETWEEN '2020-03-01' AND '2020-03-31';


+------------+
| COUNT( * ) |
+------------+
| 0          |
+------------+
1 row in set
Time: 2.077s

表分区信息

+----------+------------+------------------------+----------+----------------+
| 分区名称 | 原分区条件 | 分区表达式             | 分区条件 | 分区中数据行数 |
+----------+------------+------------------------+----------+----------------+
| p201701  | 2017-02-01 | to_days(`seller_date`) | 736726   | 12908885       |
| p201702  | 2017-03-01 | to_days(`seller_date`) | 736754   | 445191         |
| p201703  | 2017-04-01 | to_days(`seller_date`) | 736785   | 572334         |
| p201704  | 2017-05-01 | to_days(`seller_date`) | 736815   | 568233         |
| p201705  | 2017-06-01 | to_days(`seller_date`) | 736846   | 641184         |
| p201706  | 2017-07-01 | to_days(`seller_date`) | 736876   | 565191         |
| p201707  | 2017-08-01 | to_days(`seller_date`) | 736907   | 545262         |
| p201708  | 2017-09-01 | to_days(`seller_date`) | 736938   | 718690         |
| p201709  | 2017-10-01 | to_days(`seller_date`) | 736968   | 647633         |
| p201710  | 2017-11-01 | to_days(`seller_date`) | 736999   | 593606         |
| p201711  | 2017-12-01 | to_days(`seller_date`) | 737029   | 639424         |
| p201712  | 2018-01-01 | to_days(`seller_date`) | 737060   | 676133         |
| p201801  | 2018-02-01 | to_days(`seller_date`) | 737091   | 767891         |
| p201802  | 2018-03-01 | to_days(`seller_date`) | 737119   | 469169         |
| p201803  | 2018-04-01 | to_days(`seller_date`) | 737150   | 629613         |
| p201804  | 2018-05-01 | to_days(`seller_date`) | 737180   | 683604         |
| p201805  | 2018-06-01 | to_days(`seller_date`) | 737211   | 752039         |
| p201806  | 2018-07-01 | to_days(`seller_date`) | 737241   | 743466         |
| p201807  | 2018-08-01 | to_days(`seller_date`) | 737272   | 823009         |
| p201808  | 2018-09-01 | to_days(`seller_date`) | 737303   | 851376         |
| p201809  | 2018-10-01 | to_days(`seller_date`) | 737333   | 818211         |
| p201810  | 2018-11-01 | to_days(`seller_date`) | 737364   | 774612         |
| p201811  | 2018-12-01 | to_days(`seller_date`) | 737394   | 791762         |
| p201812  | 2019-01-01 | to_days(`seller_date`) | 737425   | 1005789        |
| p201901  | 2019-02-01 | to_days(`seller_date`) | 737456   | 1219524        |
| p201902  | 2019-03-01 | to_days(`seller_date`) | 737484   | 683138         |
| p201903  | 2019-04-01 | to_days(`seller_date`) | 737515   | 1046241        |
| p201904  | 2019-05-01 | to_days(`seller_date`) | 737545   | 1086249        |
| p201905  | 2019-06-01 | to_days(`seller_date`) | 737576   | 1101026        |
| p201906  | 2019-07-01 | to_days(`seller_date`) | 737606   | 1035751        |
| p201907  | 2019-08-01 | to_days(`seller_date`) | 737637   | 1121364        |
| p201908  | 2019-09-01 | to_days(`seller_date`) | 737668   | 1124801        |
| p201909  | 2019-10-01 | to_days(`seller_date`) | 737698   | 1152249        |
| p201910  | 2019-11-01 | to_days(`seller_date`) | 737729   | 1137912        |
| p201911  | 2019-12-01 | to_days(`seller_date`) | 737759   | 1143472        |
| p201912  | 2020-01-01 | to_days(`seller_date`) | 737790   | 1212102        |
| p202001  | 2020-02-01 | to_days(`seller_date`) | 737821   | 1138481        |
| p202002  | 2020-03-01 | to_days(`seller_date`) | 737850   | 540885         |
| p202003  | 2020-04-01 | to_days(`seller_date`) | 737881   | 81197          |
| p202004  | 2020-05-01 | to_days(`seller_date`) | 737911   | 0              |
| p202005  | 2020-06-01 | to_days(`seller_date`) | 737942   | 0              |
| p202006  | 2020-07-01 | to_days(`seller_date`) | 737972   | 0              |
| p202007  | 2020-08-01 | to_days(`seller_date`) | 738003   | 0              |
| p202008  | 2020-09-01 | to_days(`seller_date`) | 738034   | 0              |
| p202009  | 2020-10-01 | to_days(`seller_date`) | 738064   | 0              |
| p202010  | 2020-11-01 | to_days(`seller_date`) | 738095   | 0              |
| p202011  | 2020-12-01 | to_days(`seller_date`) | 738125   | 0              |
| p202012  | 2021-01-01 | to_days(`seller_date`) | 738156   | 0              |
| p202101  | 2021-02-01 | to_days(`seller_date`) | 738187   | 0              |
| p202102  | 2021-03-01 | to_days(`seller_date`) | 738215   | 0              |
| p202103  | 2021-04-01 | to_days(`seller_date`) | 738246   | 0              |
| p202104  | 2021-05-01 | to_days(`seller_date`) | 738276   | 0              |
| p202105  | 2021-06-01 | to_days(`seller_date`) | 738307   | 0              |
| p202106  | 2021-07-01 | to_days(`seller_date`) | 738337   | 0              |

  1. 麻烦执行下explain analyze sql 看下是否走2017分区
  2. show create table结果方便上传吗?

分区裁剪不掉第一个分区是已知问题 ,不影响正确性,会对性能稍有些影响,github 上有类似 issue ,研发后面会根据优先级安排修复。

哦那我就明白了,我可以将第一个分区的数据,按照实际情况,让它变的小,或者数据就是0, 这样就可以绕过这个问题了。

可以的,如果遇到相关问题,可以先到 github 上搜索 issue/pr,以及相关 project 了解进度。

好的谢谢老师

:+1: