Partition pruning is not effective, resulting in a full table scan

Translated
1

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

Original topic: 分区裁剪不生效，走全表查询

| username: foxchan

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.1.5
[Reproduction Path] Suddenly happened
[Encountered Problem: Problem Phenomenon and Impact]
Partition pruning is not effective, resulting in low query efficiency
Table Structure

CREATE TABLE `firpt_customer_revenue_month` (
  `Report_Date` varchar(25) NOT NULL COMMENT 'Format: yyyy-mm-dd',
  `Customer_Id` varchar(100) NOT NULL COMMENT 'Customer ID in Yixin Finance System',
  `Media_Type` int(11) NOT NULL COMMENT 'yxt_finance_platform_media.id e.g., 1: Yixin, 2: Guangdiantong, 3: WeChat, 4: Toutiao, 5: Kuaishou, 6: Qianchuan',
  `Confirm_Type` int(11) DEFAULT '2' COMMENT '1: Confirmed; 2: Unconfirmed',
  `Consume_Cash` bigint(20) DEFAULT NULL COMMENT 'Consumption Amount - Cash; Unit: cents',
  `Consume_Virtual` bigint(20) DEFAULT NULL COMMENT 'Consumption Amount - Virtual Money; Unit: cents',
  `Consume_Credit` bigint(20) DEFAULT NULL COMMENT 'Consumption Amount - Credit; Unit: cents',
  `Consume_Compensate` bigint(20) DEFAULT NULL,
  `Charge_Cash` bigint(20) DEFAULT NULL COMMENT 'Recharge Amount - Cash; Unit: cents',
  `Charge_Virtual` bigint(20) DEFAULT NULL COMMENT 'Recharge Amount - Virtual Money; Unit: cents',
  `Charge_Credit` bigint(20) DEFAULT NULL COMMENT 'Recharge Amount - Credit; Unit: cents',
  `Charge_Compensate` bigint(20) DEFAULT NULL,
  `Cost_Rebate` bigint(20) DEFAULT NULL COMMENT 'Cost - Rebate; Unit: cents',
  `Cost_Adjust` bigint(20) DEFAULT NULL,
  `Income` bigint(20) DEFAULT NULL,
  `Cfm_Income` bigint(20) DEFAULT NULL,
  `Cfm_Rebate` bigint(20) DEFAULT NULL,
  `Cfm_Adjust` bigint(20) DEFAULT NULL,
  `Cost_Cancel` bigint(20) DEFAULT NULL COMMENT 'Cost - Cancellation; Unit: cents',
  `Incomec_Charge` bigint(20) DEFAULT NULL COMMENT 'Receivable (Confirmed) - Based on Recharge; Unit: cents',
  `Uncfm_Income` bigint(20) DEFAULT NULL,
  `Uncfm_Rebate` bigint(20) DEFAULT NULL,
  `Incomec_Consume` bigint(20) DEFAULT NULL COMMENT 'Receivable (Confirmed) - Based on Consumption; Unit: cents',
  `Incomeu_Charge` bigint(20) DEFAULT NULL COMMENT 'Receivable (Unconfirmed) - Based on Recharge; Unit: cents',
  `Incomeu_Consume` bigint(20) DEFAULT NULL COMMENT 'Receivable (Unconfirmed) - Based on Consumption; Unit: cents',
  `retuan_all` bigint(20) DEFAULT NULL COMMENT 'Financial Return: Unit: cents',
  `Retuan_Charge` bigint(20) DEFAULT NULL COMMENT 'Return - Based on Recharge; Unit: cents',
  `Retuan_Consume` bigint(20) DEFAULT NULL COMMENT 'Return - Based on Consumption; Unit: cents',
  `retuan_all_real` bigint(20) DEFAULT NULL COMMENT 'Return - Financial Return - Actual Return for the Month: Unit: cents',
  `debt_all` bigint(20) DEFAULT NULL COMMENT 'Debt; Unit: cents',
  `Debt_Charge` bigint(20) DEFAULT NULL COMMENT 'Debt - Based on Recharge; Unit: cents',
  `Debt_Consume` bigint(20) DEFAULT NULL COMMENT 'Debt - Based on Consumption; Unit: cents',
  `debt_all_begining` bigint(20) DEFAULT NULL COMMENT 'Unreturned - Beginning Financial Receivable (Taken from: Last Period End Financial Receivable): Unit: cents',
  `debt_all_ending` bigint(20) DEFAULT NULL COMMENT 'Unreturned - End Financial Receivable (Taken from: Current Period Beginning Financial Receivable + Receivable Income + Adjustment - Financial Return): Unit: cents',
  `Overdue_Total` bigint(20) DEFAULT NULL COMMENT 'Overdue - Total Amount; Unit: cents',
  `Overdue_D15` bigint(20) DEFAULT NULL COMMENT 'Overdue - Within 15 Days; Unit: cents',
  `Overdue_D30` bigint(20) DEFAULT NULL COMMENT 'Overdue - Within 30 Days; Unit: cents',
  `Overdue_D60` bigint(20) DEFAULT NULL COMMENT 'Overdue - Within 60 Days; Unit: cents',
  `Last_Modify_Time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Update Time',
  `Used_Contract_Rebate` int(11) DEFAULT NULL COMMENT 'Contract Rebate Ratio in yxt_finance_contract table (value * 10000, saved with precision 0.01, e.g., 1% is saved as 100)',
  `Overdue_G60` bigint(20) DEFAULT NULL COMMENT 'Overdue for More Than 60 Days; Unit: cents',
  `cost_billed_reality_amount` bigint(20) DEFAULT NULL COMMENT 'Settlement Amount Based on Consumption',
  `recharge_billed_reality_amount` bigint(20) DEFAULT NULL COMMENT 'Settlement Amount Based on Recharge',
  `last_modify_user` varchar(200) DEFAULT NULL COMMENT 'Last Modifier',
  `unused_receipt_amount_to_last_month` bigint(20) DEFAULT NULL COMMENT 'Accumulate Unused Received Amount to the Last Month',
  `not_collect_adjust` bigint(20) DEFAULT NULL COMMENT 'Settlement Adjustment Amount Not Included in Revenue Report Statistics',
  `Overdue_Debt` bigint(20) DEFAULT NULL COMMENT 'Overdue Unreturned Amount; Unit: cents',
  `Total_Consumed` bigint(20) DEFAULT NULL COMMENT 'Total Consumption - Sum of All Consumptions; Unit: cents',
  `Float_Service_Amount` bigint(20) DEFAULT NULL COMMENT 'Floating Service Fee; Unit: cents',
  `Cfm_Float_Service_Amount` bigint(20) DEFAULT NULL COMMENT 'Settled - Floating Service Fee; Unit: cents',
  `Uncfm_Float_Service_Amount` bigint(20) DEFAULT NULL COMMENT 'Unsettled - Floating Service Fee; Unit: cents',
  `Emar_Contract_Entity` varchar(100) NOT NULL DEFAULT '0' COMMENT 'Yima Contract Entity',
  `Emar_Receipt_Entity` varchar(512) DEFAULT NULL COMMENT 'Yima Receipt Entity',
  `Customer_Receipt_Entity` varchar(1512) DEFAULT NULL COMMENT 'Customer Receipt Entity',
  PRIMARY KEY (`Customer_Id`,`Media_Type`,`Emar_Contract_Entity`,`Report_Date`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Finance Report for calculating customer''s revenue'
PARTITION BY RANGE COLUMNS(`Report_Date`)
(PARTITION `p0` VALUES LESS THAN ("2019-06-01"),
 PARTITION `p1` VALUES LESS THAN ("2019-07-01"),
 PARTITION `p2` VALUES LESS THAN ("2019-08-01"),
 PARTITION `p3` VALUES LESS THAN ("2019-09-01"),
 PARTITION `p4` VALUES LESS THAN ("2019-10-01"),
 PARTITION `p5` VALUES LESS THAN ("2019-11-01"),
 PARTITION `p6` VALUES LESS THAN ("2019-12-01"),
 PARTITION `p7` VALUES LESS THAN ("2020-01-01"),
 PARTITION `p8` VALUES LESS THAN ("2020-02-01"),
 PARTITION `p9` VALUES LESS THAN ("2020-03-01"),
 PARTITION `p10` VALUES LESS THAN ("2020-04-01"),
 PARTITION `p11` VALUES LESS THAN ("2020-05-01"),
 PARTITION `p12` VALUES LESS THAN ("2020-06-01"),
 PARTITION `p13` VALUES LESS THAN ("2020-07-01"),
 PARTITION `p202007` VALUES LESS THAN ("2020-08-01"),
 PARTITION `p202008` VALUES LESS THAN ("2020-09-01"),
 PARTITION `p202009` VALUES LESS THAN ("2020-10-01"),
 PARTITION `p202010` VALUES LESS THAN ("2020-11-01"),
 PARTITION `p202011` VALUES LESS THAN ("2020-12-01"),
 PARTITION `p202012` VALUES LESS THAN ("2021-01-01"),
 PARTITION `p202101` VALUES LESS THAN ("2021-02-01"),
 PARTITION `p202102` VALUES LESS THAN ("2021-03-01"),
 PARTITION `p202103` VALUES LESS THAN ("2021-04-01"),
 PARTITION `p202104` VALUES LESS THAN ("2021-05-01"),
 PARTITION `p202105` VALUES LESS THAN ("2021-06-01"),
 PARTITION `p202106` VALUES LESS THAN ("2021-07-01"),
 PARTITION `p202107` VALUES LESS THAN ("2021-08-01"),
 PARTITION `p202108` VALUES LESS THAN ("2021-09-01"),
 PARTITION `p202109` VALUES LESS THAN ("2021-10-01"),
 PARTITION `p202110` VALUES LESS THAN ("2021-11-01"),
 PARTITION `p202111` VALUES LESS THAN ("2021-12-01"),
 PARTITION `p202112` VALUES LESS THAN ("2022-01-01"),
 PARTITION `p202201` VALUES LESS THAN ("2022-02-01"),
 PARTITION `p202202` VALUES LESS THAN ("2022-03-01"),
 PARTITION `p202203` VALUES LESS THAN ("2022-04-01"),
 PARTITION `p202204` VALUES LESS THAN ("2022-05-01"),
 PARTITION `p202205` VALUES LESS THAN ("2022-06-01"),
 PARTITION `p202206` VALUES LESS THAN ("2022-07-01"),
 PARTITION `p202207` VALUES LESS THAN ("2022-08-01"),
 PARTITION `p202208` VALUES LESS THAN ("2022-09-01"),
 PARTITION `p202209` VALUES LESS THAN ("2022-10-01"),
 PARTITION `p202210` VALUES LESS THAN ("2022-11-01"),
 PARTITION `p202211` VALUES LESS THAN ("2022-12-01"),
 PARTITION `p202212` VALUES LESS THAN ("2023-01-01"),
 PARTITION `p202301` VALUES LESS THAN ("2023-02-01"),
 PARTITION `p202302` VALUES LESS THAN ("2023-03-01"),
 PARTITION `p202303` VALUES LESS THAN ("2023-04-01"),
 PARTITION `p202304` VALUES LESS THAN ("2023-05-01"),
 PARTITION `p202305` VALUES LESS THAN ("2023-06-01"),
 PARTITION `p202306` VALUES LESS THAN ("2023-07-01"),
 PARTITION `p202307` VALUES LESS THAN ("2023-08-01"),
 PARTITION `p202308` VALUES LESS THAN ("2023-09-01"),
 PARTITION `p202309` VALUES LESS THAN ("2023-10-01"),
 PARTITION `p202310` VALUES LESS THAN ("2023-11-01"),
 PARTITION `p202311` VALUES LESS THAN ("2023-12-01"))

image
image816×187 9.92 KB

2
| username: 有猫万事足 | Original post link

This means that this partition was accessed and only this partition was scanned.

3
| username: 像风一样的男子 | Original post link

Check the p202301 to see how many records there are. The SQL scanned 17,843 records. See if it matches.

4
| username: TiDBer_小阿飞 | Original post link

Isn’t this a scan of a partitioned table? It only read this partition.

5
| username: foxchan | Original post link

First, it scans the entire table, then reads the partition.
My other TiDB database works normally.

6
| username: foxchan | Original post link

The official example also states that it should directly read partition data instead of scanning the entire table first.

image
image1031×493 23.2 KB

7
| username: 像风一样的男子 | Original post link

Try running an analyze on this table and then test it again.

8
| username: heming | Original post link

All have been analyzed. It seems that these old clusters, which were upgraded from 4.0 to 5.x and then to 6.1, all have this issue. Newly created 6.1 clusters do not have this problem.

9
| username: foxchan | Original post link

Tried, doesn’t work.

image
image789×634 38.5 KB

10
| username: h5n1 | Original post link

Check the value of the variable for dynamic partition pruning: tidb_partition_prune_mode

11
| username: heming | Original post link

The old cluster is not quite normal:
image

New cluster
mysql> show variables like ‘%tidb_partition_prune_mode%’;
±--------------------------±-------+
| Variable_name | Value |
±--------------------------±-------+
| tidb_partition_prune_mode | static |

12
| username: h5n1 | Original post link

Change session level to static and test it.

13
| username: heming | Original post link

Great, it’s done. So it’s like a new cluster, changing everything globally to static, right?

14
| username: foxchan | Original post link

So, dynamic pruning is not as efficient as static pruning for single partition queries, right?

15
| username: 像风一样的男子 | Original post link

After enabling the global dynamic pruning mode, you need to manually trigger an analyze to update the summary statistics; otherwise, the execution plan will be inaccurate.

image
image1107×399 17.5 KB

16
| username: heming | Original post link

Since v6.3.0, the default value of this variable has been changed to dynamic. It should be more accurate to use dynamic.

17
| username: 有猫万事足 | Original post link

I tried it myself.

Dynamic

2222
22221319×190 12.2 KB

Static

1111
11111338×683 51.5 KB

I didn’t feel any difference in speed.

Compared to this example, the only difference might be the index, which I also can’t rule out.

18
| username: heming | Original post link

The actual performance might not differ much. Is it possible that the display format of the execution plan for dynamic and static views is different?

19
| username: h5n1 | Original post link

Dynamic partition pruning GA is relatively late, and the probability of encountering problems with upgraded versions is high. Static pruning with multiple partition scans has an unfriendly execution plan and does not support index join. If all the SQL queries are simple and involve partition keys, it shouldn’t have much impact.

20
| username: zhanggame1 | Original post link

Are there any parameter issues? Some parameter settings might be incorrect after the upgrade.