Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
-
Unexpected results
-
Q1/2026 Server Development, Q1/2026 Server Maintenance
Description
Looking at a testcase from mariadb-pavithrapandith for testing MDEV-37330.
--source include/have_partition.inc
|
--source include/have_sequence.inc
|
|
|
CREATE TABLE t1 ( |
c int, |
d INT, |
filler varchar(100), |
INDEX idx1(c , d ) |
) PARTITION BY RANGE COLUMNS(c, d) ( |
PARTITION `p1` VALUES LESS THAN (1, 1), |
PARTITION `p2` VALUES LESS THAN (2, 2), |
PARTITION `p3` VALUES LESS THAN (3, 3), |
PARTITION `p4` VALUES LESS THAN (4, 4), |
PARTITION `p5` VALUES LESS THAN (MAXVALUE, MAXVALUE) |
);
|
|
|
insert into t1 |
select t2.seq, t3.seq, 'filler' from seq_1_to_5 t2, seq_1_to_5 t3; |
flush status;
|
|
analyze format=json select c,d from t1 order by d limit 16; |
analyze format=json select c,d from t1 order by c,d limit 16; |
analyze format=json select c,d from t1 order by d limit 16; |
|
|
drop table t1; |
The first ANALYZE output doesn't have "partitions_index_scan_method":
analyze format=json select c,d from t1 order by d limit 16;
|
ANALYZE
|
{
|
"query_optimization": { |
"r_total_time_ms": 0.07624027 |
},
|
"query_block": { |
"select_id": 1, |
"cost": 0.010208892, |
"r_loops": 1, |
"r_total_time_ms": 0.154441037, |
"nested_loop": [ |
{
|
"read_sorted_file": { |
"r_rows": 16, |
"filesort": { |
"sort_key": "t1.d", |
"r_loops": 1, |
"r_total_time_ms": 0.10853847, |
"r_limit": 16, |
"r_used_priority_queue": true, |
"r_output_rows": 17, |
"r_sort_mode": "sort_key,addon_fields", |
"table": { |
"table_name": "t1", |
"partitions": ["p1", "p2", "p3", "p4", "p5"], |
"access_type": "index", |
"key": "idx1", |
The second one does have it:
analyze format=json select c,d from t1 order by c,d limit 16;
|
ANALYZE
|
{
|
"query_optimization": { |
"r_total_time_ms": 0.063087991 |
},
|
"query_block": { |
"select_id": 1, |
"cost": 0.010208892, |
"r_loops": 1, |
"r_total_time_ms": 0.10106424, |
"nested_loop": [ |
{
|
"table": { |
"table_name": "t1", |
"partitions": ["p1", "p2", "p3", "p4", "p5"], |
"partitions_index_scan_method": "iterate_over_partitions", |
"access_type": "index", |
Then, we run the same query as the first one and we do get partitions_index_scan_method :
analyze format=json select c,d from t1 order by d limit 16;
|
ANALYZE
|
{
|
"query_optimization": { |
"r_total_time_ms": 0.050719041 |
},
|
"query_block": { |
"select_id": 1, |
"cost": 0.010208892, |
"r_loops": 1, |
"r_total_time_ms": 0.126732398, |
"nested_loop": [ |
{
|
"read_sorted_file": { |
"r_rows": 16, |
"filesort": { |
"sort_key": "t1.d", |
"r_loops": 1, |
"r_total_time_ms": 0.088664862, |
"r_limit": 16, |
"r_used_priority_queue": true, |
"r_output_rows": 17, |
"r_sort_mode": "sort_key,addon_fields", |
"table": { |
"table_name": "t1", |
"partitions": ["p1", "p2", "p3", "p4", "p5"], |
"partitions_index_scan_method": "iterate_over_partitions", |
"access_type": "index", |
Attached mtr files to make sure I don't mix anything up.
Attachments
Issue Links
- is caused by
-
MDEV-37330 Ordered scans over PARTITION BY RANGE should not use priority queue
-
- Closed
-
- relates to
-
-
- Closed
-
- split to
-
-
- Open
-