[MDEV-27287] Range optimizer creates too wide range for kp1>=const AND kp2... Created: 2021-12-16  Updated: 2021-12-21

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.2, 10.8
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Sergei Petrunia Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

(The idea of this was given by igor, for descending indexes. Then, I modified the example so it applies to ascending indexes)

Range optimizer creates a range that's wider than necessary for conditions in this and similar forms:

CREATE INDEX idx(kp1, kp2) ON ...;
 
 
 
SELECT ... FROM t WHERE kp1 >= const1 AND kp2 < const2;

The range is:

const1 <= kp1

while one could use a narrower collection o franges:

( kp1=const1 AND kp2 < const2) OR
 
(kp1>const)

A testcase:

create table t1 (
 
  kp1 int not null,
 
  kp2 int not null,
 
  key kp(kp1, kp2)
 
);
 
insert into t1 select A.seq, B.seq from seq_1_to_100 A, seq_1_to_100 B;


set optimizer_trace=1;
 
explain select * from t1 force index(kp) where kp1>=10 and kp2 <50;


+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
 
|    1 | SIMPLE      | t1    | range | kp            | kp   | 4       | NULL | 5000 | Using where; Using index |
 
+------+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

Note key_len=4, it uses one key part

Optimizer trace confirms this:

select json_detailed(json_extract(trace,'$**.range_scan_alternatives))
 
from information_schema.optimizer_trace\G


json_detailed(json_extract(trace,'$**.range_scan_alternatives')): [
 
    [
 
        {
 
            "index": "kp",
 
            "ranges": 
            [
 
                "(10) <= (kp1)"
 
            ],
 
...


Generated at Thu Feb 08 09:51:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.