TiFlash query performance is not expected under small partition table #10487
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
-- create a partition table with 12 partitions
CREATE TABLE reports_part (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL
) PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-02-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-03-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-04-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-05-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-06-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-07-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-08-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-09-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-10-01 00:00:00') ),
PARTITION p10 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-11-01 00:00:00') ),
PARTITION p11 VALUES LESS THAN ( UNIX_TIMESTAMP('2025-12-01 00:00:00') ),
PARTITION p12 VALUES LESS THAN ( UNIX_TIMESTAMP('2026-01-01 00:00:00') ),
PARTITION p_max VALUES LESS THAN (MAXVALUE)
);
-- insert some basic rows to the table
insert into reports_part (report_id, report_status, report_updated)
values (1, "OK", "2025-01-02"),(1, "OK", "2025-01-02"),
(1, "OK", "2025-01-02"),(1, "OK", "2025-01-02"),
(1, "OK", "2025-01-02"),(1, "OK", "2025-01-02"),
(1, "OK", "2025-01-02"),(1, "OK", "2025-01-02");
insert into reports_part (report_id, report_status, report_updated)
values (2, "OK", "2025-02-02"),(2, "OK", "2025-02-02"),
(2, "OK", "2025-02-02"),(2, "OK", "2025-02-02"),
(2, "OK", "2025-02-02"),(2, "OK", "2025-02-02"),
(2, "OK", "2025-02-02"),(2, "OK", "2025-02-02");
insert into reports_part (report_id, report_status, report_updated)
values (3, "OK", "2025-03-02"),(3, "OK", "2025-03-02"),
(3, "OK", "2025-03-02"),(3, "OK", "2025-03-02"),
(3, "OK", "2025-03-02"),(3, "OK", "2025-03-02"),
(3, "OK", "2025-03-02"),(3, "OK", "2025-03-02");
insert into reports_part (report_id, report_status, report_updated)
values (4, "OK", "2025-04-02"),(4, "OK", "2025-04-02"),
(4, "OK", "2025-04-02"),(4, "OK", "2025-04-02"),
(4, "OK", "2025-04-02"),(4, "OK", "2025-04-02"),
(4, "OK", "2025-04-02"),(4, "OK", "2025-04-02");
insert into reports_part (report_id, report_status, report_updated)
values (5, "OK", "2025-05-02"),(5, "OK", "2025-05-02"),
(5, "OK", "2025-05-02"),(5, "OK", "2025-05-02"),
(5, "OK", "2025-05-02"),(5, "OK", "2025-05-02"),
(5, "OK", "2025-05-02"),(5, "OK", "2025-05-02");
insert into reports_part (report_id, report_status, report_updated)
values (6, "OK", "2025-06-02"),(6, "OK", "2025-06-02"),
(6, "OK", "2025-06-02"),(6, "OK", "2025-06-02"),
(6, "OK", "2025-06-02"),(6, "OK", "2025-06-02"),
(6, "OK", "2025-06-02"),(6, "OK", "2025-06-02");
insert into reports_part (report_id, report_status, report_updated)
values (7, "OK", "2025-07-02"),(7, "OK", "2025-07-02"),
(7, "OK", "2025-07-02"),(7, "OK", "2025-07-02"),
(7, "OK", "2025-07-02"),(7, "OK", "2025-07-02"),
(7, "OK", "2025-07-02"),(7, "OK", "2025-07-02");
insert into reports_part (report_id, report_status, report_updated)
values (8, "OK", "2025-08-02"),(8, "OK", "2025-08-02"),
(8, "OK", "2025-08-02"),(8, "OK", "2025-08-02"),
(8, "OK", "2025-08-02"),(8, "OK", "2025-08-02"),
(8, "OK", "2025-08-02"),(8, "OK", "2025-08-02");
insert into reports_part (report_id, report_status, report_updated)
values (9, "OK", "2025-09-02"),(9, "OK", "2025-09-02"),
(9, "OK", "2025-09-02"),(9, "OK", "2025-09-02"),
(9, "OK", "2025-09-02"),(9, "OK", "2025-09-02"),
(9, "OK", "2025-09-02"),(9, "OK", "2025-09-02");
insert into reports_part (report_id, report_status, report_updated)
values (10, "OK", "2025-10-02"),(10, "OK", "2025-10-02"),
(10, "OK", "2025-10-02"),(10, "OK", "2025-10-02"),
(10, "OK", "2025-10-02"),(10, "OK", "2025-10-02"),
(10, "OK", "2025-10-02"),(10, "OK", "2025-10-02");
insert into reports_part (report_id, report_status, report_updated)
values (11, "OK", "2025-11-02"),(11, "OK", "2025-11-02"),
(11, "OK", "2025-11-02"),(11, "OK", "2025-11-02"),
(11, "OK", "2025-11-02"),(11, "OK", "2025-11-02"),
(11, "OK", "2025-11-02"),(11, "OK", "2025-11-02");
insert into reports_part (report_id, report_status, report_updated)
values (12, "OK", "2025-12-02"),(12, "OK", "2025-12-02"),
(12, "OK", "2025-12-02"),(12, "OK", "2025-12-02"),
(12, "OK", "2025-12-02"),(12, "OK", "2025-12-02"),
(12, "OK", "2025-12-02"),(12, "OK", "2025-12-02");
-- fill the partitions repeatedly
insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;
insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;
insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;
insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;insert into reports_part select * from reports_part;
-- create the non-partition table
CREATE TABLE reports (
report_id INT NOT NULL,
report_status VARCHAR(20) NOT NULL,
report_updated TIMESTAMP NOT NULL
);
insert into reports select * from reports_part;
alter table reports_part set tiflash replica 1;
alter table reports set tiflash replica 1;
compare the time elapsed for selecting from partition table and non-partition table
select "p0-0",count(*) from reports_part partition(p0);
select "p0-1",count(*) from reports_part partition(p0,p1);
select "p0-2",count(*) from reports_part partition(p0,p1,p2);
select "p0-3",count(*) from reports_part partition(p0,p1,p2,p3);
select "p0-4",count(*) from reports_part partition(p0,p1,p2,p3,p4);
select "p0-5",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5);
select "p0-6",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6);
select "p0-7",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7);
select "p0-8",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7,p8);
select "p0-9",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7,p8,p9);
select "p0-10",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10);
select "p0-11",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11);
select "p0-12",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12);
select "non-part",count(*) from reports;
2. What did you expect to see? (Required)
scanning the same number of rows on partition table is the same as non-partition table
3. What did you see instead (Required)
scanning the same number of rows on partition table is slower than non-partition table
-- master
-- we can observe performance regression as the number of partition increased
-- and scanning the same number of rows on partition table is slower than non-partition table
TiDB root@10.2.12.81:test> select "p0-0",count(*) from reports_part partition(p0);
-> select "p0-1",count(*) from reports_part partition(p0,p1);
-> select "p0-2",count(*) from reports_part partition(p0,p1,p2);
-> select "p0-3",count(*) from reports_part partition(p0,p1,p2,p3);
-> select "p0-4",count(*) from reports_part partition(p0,p1,p2,p3,p4);
-> select "p0-5",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5);
-> select "p0-6",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6);
-> select "p0-7",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7);
-> select "p0-8",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7,p8);
-> select "p0-9",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7,p8,p9);
-> select "p0-10",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10);
-> select "p0-11",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11);
-> select "p0-12",count(*) from reports_part partition(p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12);
-> select "non-part",count(*) from reports;
+------+----------+
| p0-0 | count(*) |
+------+----------+
| p0-0 | 0 |
+------+----------+
1 row in set
Time: 0.025s
+------+----------+
| p0-1 | count(*) |
+------+----------+
| p0-1 | 524288 |
+------+----------+
1 row in set
Time: 0.014s
+------+----------+
| p0-2 | count(*) |
+------+----------+
| p0-2 | 1048576 |
+------+----------+
1 row in set
Time: 0.017s
+------+----------+
| p0-3 | count(*) |
+------+----------+
| p0-3 | 1572864 |
+------+----------+
1 row in set
Time: 0.019s
+------+----------+
| p0-4 | count(*) |
+------+----------+
| p0-4 | 2097152 |
+------+----------+
1 row in set
Time: 0.023s
+------+----------+
| p0-5 | count(*) |
+------+----------+
| p0-5 | 2621440 |
+------+----------+
1 row in set
Time: 0.023s
+------+----------+
| p0-6 | count(*) |
+------+----------+
| p0-6 | 3145728 |
+------+----------+
1 row in set
Time: 0.024s
+------+----------+
| p0-7 | count(*) |
+------+----------+
| p0-7 | 3670016 |
+------+----------+
1 row in set
Time: 0.027s
+------+----------+
| p0-8 | count(*) |
+------+----------+
| p0-8 | 4194304 |
+------+----------+
1 row in set
Time: 0.029s
+------+----------+
| p0-9 | count(*) |
+------+----------+
| p0-9 | 4718592 |
+------+----------+
1 row in set
Time: 0.031s
+-------+----------+
| p0-10 | count(*) |
+-------+----------+
| p0-10 | 5242880 |
+-------+----------+
1 row in set
Time: 0.036s
+-------+----------+
| p0-11 | count(*) |
+-------+----------+
| p0-11 | 5767168 |
+-------+----------+
1 row in set
Time: 0.036s
+-------+----------+
| p0-12 | count(*) |
+-------+----------+
| p0-12 | 6291456 |
+-------+----------+
1 row in set
Time: 0.040s
+----------+----------+
| non-part | count(*) |
+----------+----------+
| non-part | 6291456 |
+----------+----------+
1 row in set
Time: 0.017s4. What is your TiFlash version? (Required)
master