Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
CREATE TABLE t1 ( |
pk1 INT, |
pk2 INT, |
f1 VARCHAR(3), |
f2 VARCHAR(1021), |
PRIMARY KEY (pk1,pk2), |
KEY(f2) |
) ENGINE=InnoDB;
|
|
|
INSERT INTO t1 VALUES (1,2,'2','abc'),(2,3,'3','def'); |
INSERT INTO t1 VALUES (2,0,'2','abc'); |
INSERT INTO t1 VALUES (2,5,'2','abc'); |
INSERT INTO t1 VALUES (2,4,'2','abc'); |
|
|
Also use |
set optimizer_use_condition_selecitivity=4; |
MariaDB [test]> analyze select * from t1 force index(f2) where pk1 <= 5 and pk2 <=5 and f2 = 'abc' and f1 <= '3';
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+------------------------------------+
|
| 1 | SIMPLE | t1 | range | f2 | f2 | 1032 | NULL | 4 | 4.00 | 80.00 | 100.00 | Using index condition; Using where |
|
+------+-------------+-------+-------+---------------+------+---------+------+------+--------+----------+------------+------------------------------------+
|
1 row in set (0.004 sec)
|
So filtered here shows 80%. The reason is we set filtered during best_access_path and there we actually picked ref access instead of range and later in plan refinement stage we switched the plan to use range access instead of ref access.
The optimizer trace shows that we picked ref access first
"considered_execution_plans": [
|
{
|
"plan_prefix": [],
|
"table": "t1",
|
"best_access_path": {
|
"considered_access_paths": [
|
{
|
"access_type": "ref",
|
"index": "f2",
|
"used_range_estimates": true,
|
"rows": 4,
|
"cost": 3,
|
"chosen": true
|
},
|
{
|
"access_type": "range",
|
"resulting_rows": 4,
|
"cost": 6.3004,
|
"chosen": false
|
}
|
]
|
},
|
"rows_for_plan": 4,
|
"cost_for_plan": 3.8,
|
"selectivity": 0.8,
|
"join_cardinality": 3.2
|
}
|
ref access on f2 is cheaper than range access on f2.
The selectivity is 0.8 (80%) which we see in the analyze output.
The issue with such cases is that for ref access estimates we sometimes use range access estimates(because they are more accurate).
One can see in the optimizer trace used_range_estimates which means that we actually used the range estimates. So if we used the range estimates we took care of the selecitivity of the entire condition.
So the selectivity here should have been 1. There is some bug while discounting selectivity for conditions that were already taken into account.
We only discount the selectivity when the range access and ref access on the index has the same keyparts and they are of the type keypart_i = const
Attachments
Issue Links
- relates to
-
MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT
-
- Stalled
-