[MDEV-29713] Optimizer Trace: best_access_path doesn't trace LooseScan quick selects correctly Created: 2022-10-05  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.7
Fix Version/s: 10.11

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


 Description   

Take group_min_max.test, for example and run the first query from there:

explain select a1, min(a2) from t1 group by a1;

EXPLAIN shows loose scan is used.
range analyser in the optimizer trace shows that, too:

    "chosen_range_access_summary": {
 
      "range_access_plan": {
 
        "type": "index_group",
 
        "index": "idx_t1_1",
 
        "min_max_arg": "a2",
 
        "min_aggregate": true,
 
        "max_aggregate": false,
 
        "distinct_aggregate": false,
 
        "rows": 5,
 
        "cost": 6.75,
 
        "key_parts_used_for_access": ["a1"],
 
        "ranges": []
 
      },

However, inside best_access_path the trace says 'index_merge' is used:

  "best_access_path": {
 
    "chosen_access_method": {
 
      "type": "index_merge",
 
      "records": 5,
 
      "cost": 6.75,
 
      "uses_join_buffering": false
 
    }

This is caused by plain wrong code in best_access_path that assumes that all quick selects that are not range selects are index_merge selects.
Generated at Thu Feb 08 10:10:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.