Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-33306

Optimizer choosing incorrect index in 10.6, 10.5 but not in 10.4

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.5, 10.6, 10.11
    • 10.6.18, 10.11.8
    • Optimizer
    • None

    Description

      Queries.sql has all the queries rrequired to reproduce issue.

      Please see the explain plans for various versions below

      CS 10.4.32. Uses correct index regardless of the value passed in the where clause

      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
      +------+-------------+-------+------+---------------+--------+---------+-------+------+---------+----------+------------+---------------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key    | key_len | ref   | rows | r_rows  | filtered | r_filtered | Extra                                                               |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+---------+----------+------------+---------------------------------------------------------------------+
      |    1 | SIMPLE      | a     | ref  | indx01        | indx01 | 52      | const | 3000 | 3000.00 |   100.00 |     100.00 | Using index condition; Using where; Using temporary; Using filesort |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+---------+----------+------------+---------------------------------------------------------------------+
      1 row in set (0.013 sec)
       
      MariaDB [test]>
      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_06' GROUP BY a.c3;
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key    | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra                                                               |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      |    1 | SIMPLE      | a     | ref  | indx01        | indx01 | 52      | const | 1    | 1.00   |   100.00 |     100.00 | Using index condition; Using where; Using temporary; Using filesort |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      1 row in set (0.005 sec)
      

      CS 10.5.23. Uses incorrect index when value passed doesn't have good selectivity

      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key    | key_len | ref  | rows  | r_rows   | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      |    1 | SIMPLE      | a     | index | indx01        | indx02 | 4       | NULL | 14697 | 15001.00 |    20.41 |      20.00 | Using where |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      1 row in set (0.083 sec)
       
      MariaDB [test]>
      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_06' GROUP BY a.c3;
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key    | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra                                                               |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      |    1 | SIMPLE      | a     | ref  | indx01        | indx01 | 52      | const | 1    | 1.00   |   100.00 |     100.00 | Using index condition; Using where; Using temporary; Using filesort |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      1 row in set (0.003 sec)
      

      CS 10.6.16. Same behaviour as 10.5.23

      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key    | key_len | ref  | rows  | r_rows   | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      |    1 | SIMPLE      | a     | index | indx01        | indx02 | 4       | NULL | 14700 | 15001.00 |    20.41 |      20.00 | Using where |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      1 row in set (0.098 sec)
       
      MariaDB [test]>
      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_06' GROUP BY a.c3;
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      | id   | select_type | table | type | possible_keys | key    | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra                                                               |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      |    1 | SIMPLE      | a     | ref  | indx01        | indx01 | 52      | const | 1    | 1.00   |   100.00 |     100.00 | Using index condition; Using where; Using temporary; Using filesort |
      +------+-------------+-------+------+---------------+--------+---------+-------+------+--------+----------+------------+---------------------------------------------------------------------+
      1 row in set (0.005 sec)
      

      Ver 10.6.16. using FORCE INDEX shows how it would scan less rows for the same query if it uses the 'correct' index. The same index that it automatically selects in 10.4

      MariaDB [test]> ANALYZE SELECT   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a FORCE INDEX (indx02)  WHERE a.c2='c2_01' GROUP BY a.c3;
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      | id   | select_type | table | type  | possible_keys | key    | key_len | ref  | rows  | r_rows   | filtered | r_filtered | Extra       |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      |    1 | SIMPLE      | a     | index | NULL          | indx02 | 4       | NULL | 14700 | 15001.00 |   100.00 |      20.00 | Using where |
      +------+-------------+-------+-------+---------------+--------+---------+------+-------+----------+----------+------------+-------------+
      

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment - - edited

            I used a test from MDEV-33306.sql to get rid of rand() and test on the same dataset on different versions. 10.5-10.11 are slower than 10.4, while 11.0+ has around the same query execution time as 10.4
            10.4

            MariaDB [test]> analyze format=json SELECT sql_no_cache   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
            {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_total_time_ms": 114.24,
                "filesort": {
                  "sort_key": "a.c3",
                  "r_loops": 1,
                  "r_total_time_ms": 0.1463,
                  "r_used_priority_queue": false,
                  "r_output_rows": 201,
                  "r_buffer_size": "4Kb",
                  "temporary_table": {
                    "table": {
                      "table_name": "a",
                      "access_type": "ref",
                      "possible_keys": ["indx01"],
                      "key": "indx01",
                      "key_length": "52",
                      "used_key_parts": ["c2"],
                      "ref": ["const"],
                      "r_loops": 1,
                      "rows": 3000,
                      "r_rows": 3000,
                      "r_total_time_ms": 104.39,
                      "filtered": 100,
                      "r_filtered": 100,
                      "index_condition": "a.c2 = 'c2_01'",
                      "attached_condition": "a.c2 <=> 'c2_01'"
                    }
                  }
                }
              }
            }
            

            10.5( and ~the same on 10.6-10.11)

            MariaDB [test]> analyze format=json SELECT sql_no_cache   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
            | {
              "query_block": {
                "select_id": 1,
                "r_loops": 1,
                "r_total_time_ms": 668.6833897,
                "table": {
                  "table_name": "a",
                  "access_type": "index",
                  "possible_keys": ["indx01"],
                  "key": "indx02",
                  "key_length": "4",
                  "used_key_parts": ["c3"],
                  "r_loops": 1,
                  "rows": 14697,
                  "r_rows": 15000,
                  "r_table_time_ms": 654.9452669,
                  "r_other_time_ms": 13.68485618,
                  "filtered": 20.41232872,
                  "r_filtered": 20,
                  "attached_condition": "a.c2 = 'c2_01'"
                }
              }
            } |
            
            

            11.0(-~11.4)

            MariaDB [test]> analyze format=json SELECT sql_no_cache   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3;
             {
              "query_optimization": {
                "r_total_time_ms": 2.986958457
              },
              "query_block": {
                "select_id": 1,
                "cost": 4.465185951,
                "r_loops": 1,
                "r_total_time_ms": 103.0670346,
                "filesort": {
                  "sort_key": "a.c3",
                  "r_loops": 1,
                  "r_total_time_ms": 0.163121247,
                  "r_used_priority_queue": false,
                  "r_output_rows": 201,
                  "r_buffer_size": "4Kb",
                  "r_sort_mode": "sort_key,rowid",
                  "temporary_table": {
                    "nested_loop": [
                      {
                        "table": {
                          "table_name": "a",
                          "access_type": "ALL",
                          "possible_keys": ["indx01"],
                          "loops": 1,
                          "r_loops": 1,
                          "rows": 14700,
                          "r_rows": 15000,
                          "cost": 2.4890156,
                          "r_table_time_ms": 84.82377979,
                          "r_other_time_ms": 17.77049086,
                          "r_engine_stats": {
                            "pages_accessed": 59
                          },
                          "filtered": 20.40816307,
                          "r_filtered": 20,
                          "attached_condition": "a.c2 = 'c2_01'"
                        }
                      }
                    ]
                  }
                }
              }
            }
            

            10.4:

            s| SELECT sql_no_cache   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3 | {
              "steps": [
                {
                  "join_preparation": {
                    "select_id": 1,
                    "steps": [
                      {
                        "expanded_query": "select sql_no_cache a.c3 AS c3,sum(a.c7) AS `SUM(a.c7)`,count(a.c1) AS `COUNT(a.c1)` from tab1 a where a.c2 = 'c2_01' group by a.c3"
                      }
                    ]
                  }
                },
                {
                  "join_optimization": {
                    "select_id": 1,
                    "steps": [
                      {
                        "condition_processing": {
                          "condition": "WHERE",
                          "original_condition": "a.c2 = 'c2_01'",
                          "steps": [
                            {
                              "transformation": "equality_propagation",
                              "resulting_condition": "multiple equal('c2_01', a.c2)"
                            },
                            {
                              "transformation": "constant_propagation",
                              "resulting_condition": "multiple equal('c2_01', a.c2)"
                            },
                            {
                              "transformation": "trivial_condition_removal",
                              "resulting_condition": "multiple equal('c2_01', a.c2)"
                            }
                          ]
                        }
                      },
                      {
                        "table_dependencies": [
                          {
                            "table": "a",
                            "row_may_be_null": false,
                            "map_bit": 0,
                            "depends_on_map_bits": []
                          }
                        ]
                      },
                      {
                        "ref_optimizer_key_uses": [
                          {
                            "table": "a",
                            "field": "c2",
                            "equals": "'c2_01'",
                            "null_rejecting": false
                          }
                        ]
                      },
                      {
                        "rows_estimation": [
                          {
                            "table": "a",
                            "range_analysis": {
                              "table_scan": {
                                "rows": 14697,
                                "cost": 3038.5
                              },
                              "potential_range_indexes": [
                                {
                                  "index": "PRIMARY",
                                  "usable": false,
                                  "cause": "not applicable"
                                },
                                {
                                  "index": "indx01",
                                  "usable": true,
                                  "key_parts": ["c2", "c7", "c1"]
                                },
                                {
                                  "index": "indx02",
                                  "usable": true,
                                  "key_parts": ["c3", "c1", "c2"]
                                },
                                {
                                  "index": "indx03",
                                  "usable": false,
                                  "cause": "not applicable"
                                },
                                {
                                  "index": "indx04",
                                  "usable": false,
                                  "cause": "not applicable"
                                }
                              ],
                              "setup_range_conditions": [],
                              "group_index_range": {
                                "chosen": false,
                                "cause": "not applicable aggregate function"
                              },
                              "analyzing_range_alternatives": {
                                "range_scan_alternatives": [
                                  {
                                    "index": "indx01",
                                    "ranges": ["(c2_01) <= (c2) <= (c2_01)"],
                                    "rowid_ordered": false,
                                    "using_mrr": false,
                                    "index_only": false,
                                    "rows": 3000,
                                    "cost": 3783.4,
                                    "chosen": false,
                                    "cause": "cost"
                                  }
                                ],
                                "analyzing_roworder_intersect": {
                                  "cause": "too few roworder scans"
                                },
                                "analyzing_index_merge_union": []
                              }
                            }
                          },
                          {
                            "selectivity_for_indexes": [
                              {
                                "index_name": "indx01",
                                "selectivity_from_index": 0.2041
                              }
                            ],
                            "selectivity_for_columns": [],
                            "cond_selectivity": 0.2041
                          }
                        ]
                      },
                      {
                        "considered_execution_plans": [
                          {
                            "plan_prefix": [],
                            "table": "a",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "ref",
                                  "index": "indx01",
                                  "used_range_estimates": true,
                                  "rowid_filter_skipped": "worst/max seeks clipping",
                                  "rows": 3000,
                                  "cost": 292,
                                  "chosen": true
                                },
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 3000,
                                  "cost": 2436.4,
                                  "chosen": false
                                }
                              ],
                              "chosen_access_method": {
                                "type": "ref",
                                "records": 3000,
                                "cost": 292,
                                "uses_join_buffering": false
                              }
                            },
                            "rows_for_plan": 3000,
                            "cost_for_plan": 892,
                            "estimated_join_cardinality": 3000
                          }
                        ]
                      },
                      {
                        "best_join_order": ["a"]
                      },
                      {
                        "attaching_conditions_to_tables": {
                          "original_condition": "a.c2 = 'c2_01'",
                          "attached_conditions_computation": [],
                          "attached_conditions_summary": [
                            {
                              "table": "a",
                              "attached": "a.c2 = 'c2_01'"
                            }
                          ]
                        }
                      },
                      {
                        "test_if_skip_sort_order": [
                          {
                            "reconsidering_access_paths_for_index_ordering": {
                              "clause": "GROUP BY",
                              "fanout": 1,
                              "read_time": 292,
                              "table": "a",
                              "rows_estimation": 3000,
                              "possible_keys": [
                                {
                                  "index": "PRIMARY",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                },
                                {
                                  "index": "indx01",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                },
                                {
                                  "index": "indx02",
                                  "can_resolve_order": true,
                                  "usable": false
                                },
                                {
                                  "index": "indx03",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                },
                                {
                                  "index": "indx04",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                }
                              ]
                            }
                          }
                        ]
                      }
                    ]
                  }
                },
                {
                  "join_execution": {
                    "select_id": 1,
                    "steps": []
                  }
                }
              ]
            }
            

            10.5 (-10.11):

             SELECT sql_no_cache   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3 | {
              "steps": [
                {
                  "join_preparation": {
                    "select_id": 1,
                    "steps": [
                      {
                        "expanded_query": "select sql_no_cache a.c3 AS c3,sum(a.c7) AS `SUM(a.c7)`,count(a.c1) AS `COUNT(a.c1)` from tab1 a where a.c2 = 'c2_01' group by a.c3"
                      }
                    ]
                  }
                },
                {
                  "join_optimization": {
                    "select_id": 1,
                    "steps": [
                      {
                        "condition_processing": {
                          "condition": "WHERE",
                          "original_condition": "a.c2 = 'c2_01'",
                          "steps": [
                            {
                              "transformation": "equality_propagation",
                              "resulting_condition": "multiple equal('c2_01', a.c2)"
                            },
                            {
                              "transformation": "constant_propagation",
                              "resulting_condition": "multiple equal('c2_01', a.c2)"
                            },
                            {
                              "transformation": "trivial_condition_removal",
                              "resulting_condition": "multiple equal('c2_01', a.c2)"
                            }
                          ]
                        }
                      },
                      {
                        "table_dependencies": [
                          {
                            "table": "a",
                            "row_may_be_null": false,
                            "map_bit": 0,
                            "depends_on_map_bits": []
                          }
                        ]
                      },
                      {
                        "ref_optimizer_key_uses": [
                          {
                            "table": "a",
                            "field": "c2",
                            "equals": "'c2_01'",
                            "null_rejecting": false
                          }
                        ]
                      },
                      {
                        "rows_estimation": [
                          {
                            "table": "a",
                            "range_analysis": {
                              "table_scan": {
                                "rows": 14697,
                                "cost": 3038.4
                              },
                              "potential_range_indexes": [
                                {
                                  "index": "PRIMARY",
                                  "usable": false,
                                  "cause": "not applicable"
                                },
                                {
                                  "index": "indx01",
                                  "usable": true,
                                  "key_parts": ["c2", "c7", "c1"]
                                },
                                {
                                  "index": "indx02",
                                  "usable": true,
                                  "key_parts": ["c3", "c1", "c2"]
                                },
                                {
                                  "index": "indx03",
                                  "usable": false,
                                  "cause": "not applicable"
                                },
                                {
                                  "index": "indx04",
                                  "usable": false,
                                  "cause": "not applicable"
                                }
                              ],
                              "setup_range_conditions": [],
                              "analyzing_range_alternatives": {
                                "range_scan_alternatives": [
                                  {
                                    "index": "indx01",
                                    "ranges": ["(c2_01) <= (c2) <= (c2_01)"],
                                    "rowid_ordered": false,
                                    "using_mrr": false,
                                    "index_only": false,
                                    "rows": 3000,
                                    "cost": 3631.392788,
                                    "chosen": false,
                                    "cause": "cost"
                                  }
                                ],
                                "analyzing_roworder_intersect": {
                                  "cause": "too few roworder scans"
                                },
                                "analyzing_index_merge_union": []
                              },
                              "group_index_range": {
                                "chosen": false,
                                "cause": "not applicable aggregate function"
                              }
                            }
                          },
                          {
                            "selectivity_for_indexes": [
                              {
                                "index_name": "indx01",
                                "selectivity_from_index": 0.20412329
                              }
                            ],
                            "selectivity_for_columns": [],
                            "cond_selectivity": 0.20412329
                          }
                        ]
                      },
                      {
                        "considered_execution_plans": [
                          {
                            "plan_prefix": [],
                            "table": "a",
                            "best_access_path": {
                              "considered_access_paths": [
                                {
                                  "access_type": "ref",
                                  "index": "indx01",
                                  "used_range_estimates": true,
                                  "rowid_filter_skipped": "worst/max seeks clipping",
                                  "rows": 3000,
                                  "cost": 3031.372788,
                                  "chosen": true
                                },
                                {
                                  "access_type": "scan",
                                  "resulting_rows": 3000,
                                  "cost": 2436.4,
                                  "chosen": true,
                                  "use_tmp_table": true
                                }
                              ],
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 3000,
                                "cost": 2436.4,
                                "uses_join_buffering": false
                              }
                            },
                            "rows_for_plan": 3000,
                            "cost_for_plan": 3036.4,
                            "cost_for_sorting": 3000,
                            "estimated_join_cardinality": 3000
                          }
                        ]
                      },
                      {
                        "best_join_order": ["a"]
                      },
                      {
                        "attaching_conditions_to_tables": {
                          "original_condition": "a.c2 = 'c2_01'",
                          "attached_conditions_computation": [],
                          "attached_conditions_summary": [
                            {
                              "table": "a",
                              "attached": "a.c2 = 'c2_01'"
                            }
                          ]
                        }
                      },
                      {
                        "test_if_skip_sort_order": [
                          {
                            "reconsidering_access_paths_for_index_ordering": {
                              "clause": "GROUP BY",
                              "fanout": 1,
                              "read_time": 2436.401,
                              "table": "a",
                              "rows_estimation": 3000,
                              "possible_keys": [
                                {
                                  "index": "PRIMARY",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                },
                                {
                                  "index": "indx01",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                },
                                {
                                  "index": "indx02",
                                  "can_resolve_order": true,
                                  "updated_limit": 14697,
                                  "index_scan_time": 14697,
                                  "records": 14697,
                                  "chosen": true
                                },
                                {
                                  "index": "indx03",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                },
                                {
                                  "index": "indx04",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                }
                              ]
                            }
                          }
                        ]
                      }
                    ]
                  }
                },
                {
                  "join_execution": {
                    "select_id": 1,
                    "steps": []
                  }
                }
              ]
            } 
            

            11.0 (-11.4)

            SELECT sql_no_cache   a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3 | {
              "steps": [
                {
                  "join_preparation": {
                    "select_id": 1,
                    "steps": [
                      {
                        "expanded_query": "select sql_no_cache a.c3 AS c3,sum(a.c7) AS `SUM(a.c7)`,count(a.c1) AS `COUNT(a.c1)` from tab1 a where a.c2 = 'c2_01' group by a.c3"
                      }
                    ]
                  }
                },
                {
                  "join_optimization": {
                    "select_id": 1,
                    "steps": [
                      {
                        "condition_processing": {
                          "condition": "WHERE",
                          "original_condition": "a.c2 = 'c2_01'",
                          "steps": [
                            {
                              "transformation": "equality_propagation",
                              "resulting_condition": "multiple equal('c2_01', a.c2)"
                            },
                            {
                              "transformation": "constant_propagation",
                              "resulting_condition": "multiple equal('c2_01', a.c2)"
                            },
                            {
                              "transformation": "trivial_condition_removal",
                              "resulting_condition": "multiple equal('c2_01', a.c2)"
                            }
                          ]
                        }
                      },
                      {
                        "table_dependencies": [
                          {
                            "table": "a",
                            "row_may_be_null": false,
                            "map_bit": 0,
                            "depends_on_map_bits": []
                          }
                        ]
                      },
                      {
                        "ref_optimizer_key_uses": [
                          {
                            "table": "a",
                            "index": "indx01",
                            "field": "c2",
                            "equals": "'c2_01'",
                            "null_rejecting": false
                          }
                        ]
                      },
                      {
                        "rows_estimation": [
                          {
                            "table": "a",
                            "range_analysis": {
                              "table_scan": {
                                "rows": 14700,
                                "cost": 2.4890156
                              },
                              "potential_range_indexes": [
                                {
                                  "index": "PRIMARY",
                                  "usable": false,
                                  "cause": "not applicable"
                                },
                                {
                                  "index": "indx01",
                                  "usable": true,
                                  "key_parts": ["c2", "c7", "c1"]
                                },
                                {
                                  "index": "indx02",
                                  "usable": true,
                                  "key_parts": ["c3", "c1", "c2"]
                                },
                                {
                                  "index": "indx03",
                                  "usable": false,
                                  "cause": "not applicable"
                                },
                                {
                                  "index": "indx04",
                                  "usable": false,
                                  "cause": "not applicable"
                                }
                              ],
                              "setup_range_conditions": [],
                              "analyzing_range_alternatives": {
                                "range_scan_alternatives": [
                                  {
                                    "index": "indx01",
                                    "ranges": ["(c2_01) <= (c2) <= (c2_01)"],
                                    "rowid_ordered": false,
                                    "using_mrr": false,
                                    "index_only": false,
                                    "rows": 3000,
                                    "cost": 3.08444424,
                                    "chosen": false,
                                    "cause": "cost"
                                  }
                                ],
                                "analyzing_roworder_intersect": {
                                  "cause": "too few roworder scans"
                                },
                                "analyzing_index_merge_union": []
                              },
                              "group_index_range": {
                                "chosen": false,
                                "cause": "not applicable aggregate function"
                              }
                            }
                          },
                          {
                            "selectivity_for_indexes": [
                              {
                                "index_name": "indx01",
                                "selectivity_from_index": 0.204081633
                              }
                            ],
                            "selectivity_for_columns": [],
                            "cond_selectivity": 0.204081633
                          }
                        ]
                      },
                      {
                        "considered_execution_plans": [
                          {
                            "plan_prefix": "",
                            "get_costs_for_tables": [
                              {
                                "best_access_path": {
                                  "table": "a",
                                  "plan_details": {
                                    "record_count": 1
                                  },
                                  "considered_access_paths": [
                                    {
                                      "access_type": "ref",
                                      "index": "indx01",
                                      "used_range_estimates": true,
                                      "rows": 3000,
                                      "cost": 3.08365312,
                                      "chosen": true
                                    },
                                    {
                                      "access_type": "scan",
                                      "rows": 14700,
                                      "rows_after_filter": 3000,
                                      "rows_out": 3000,
                                      "cost": 2.4890156,
                                      "index_only": false,
                                      "chosen": true,
                                      "use_tmp_table": true
                                    }
                                  ],
                                  "chosen_access_method": {
                                    "type": "scan",
                                    "rows_read": 3000,
                                    "rows_out": 3000,
                                    "cost": 2.4890156,
                                    "uses_join_buffering": false
                                  }
                                }
                              }
                            ]
                          },
                          {
                            "plan_prefix": "",
                            "table": "a",
                            "rows_for_plan": 3000,
                            "cost_for_plan": 2.4890156,
                            "cost_for_sorting": 1.976170351
                          }
                        ]
                      },
                      {
                        "best_join_order": ["a"],
                        "rows": 3000,
                        "cost": 4.465185951
                      },
                      {
                        "substitute_best_equal": {
                          "condition": "WHERE",
                          "resulting_condition": "a.c2 = 'c2_01'"
                        }
                      },
                      {
                        "attaching_conditions_to_tables": {
                          "attached_conditions_computation": [],
                          "attached_conditions_summary": [
                            {
                              "table": "a",
                              "attached_condition": "a.c2 = 'c2_01'"
                            }
                          ]
                        }
                      },
                      {
                        "make_join_readinfo": []
                      },
                      {
                        "test_if_skip_sort_order": [
                          {
                            "reconsidering_access_paths_for_index_ordering": {
                              "clause": "GROUP BY",
                              "table": "a",
                              "rows_estimation": 3000,
                              "filesort_cost": 0.356339442,
                              "read_cost": 2.845355042,
                              "filesort_type": "merge_sort with addon fields",
                              "fanout": 1,
                              "possible_keys": [
                                {
                                  "index": "PRIMARY",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                },
                                {
                                  "index": "indx01",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                },
                                {
                                  "index": "indx02",
                                  "can_resolve_order": true,
                                  "direction": 1,
                                  "rows_to_examine": 14700,
                                  "range_scan": false,
                                  "scan_cost": 14.74536712,
                                  "usable": false,
                                  "cause": "cost"
                                },
                                {
                                  "index": "indx03",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                },
                                {
                                  "index": "indx04",
                                  "can_resolve_order": false,
                                  "cause": "not usable index for the query"
                                }
                              ]
                            }
                          }
                        ]
                      }
                    ]
                  }
                },
                {
                  "join_execution": {
                    "select_id": 1,
                    "steps": []
                  }
                }
              ]
            }
            

            alice Alice Sherepa added a comment - - edited I used a test from MDEV-33306 .sql to get rid of rand() and test on the same dataset on different versions. 10.5-10.11 are slower than 10.4, while 11.0+ has around the same query execution time as 10.4 10.4 MariaDB [test]> analyze format=json SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3; { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 114.24, "filesort": { "sort_key": "a.c3", "r_loops": 1, "r_total_time_ms": 0.1463, "r_used_priority_queue": false, "r_output_rows": 201, "r_buffer_size": "4Kb", "temporary_table": { "table": { "table_name": "a", "access_type": "ref", "possible_keys": ["indx01"], "key": "indx01", "key_length": "52", "used_key_parts": ["c2"], "ref": ["const"], "r_loops": 1, "rows": 3000, "r_rows": 3000, "r_total_time_ms": 104.39, "filtered": 100, "r_filtered": 100, "index_condition": "a.c2 = 'c2_01'", "attached_condition": "a.c2 <=> 'c2_01'" } } } } } 10.5( and ~the same on 10.6-10.11) MariaDB [test]> analyze format=json SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3; | { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 668.6833897, "table": { "table_name": "a", "access_type": "index", "possible_keys": ["indx01"], "key": "indx02", "key_length": "4", "used_key_parts": ["c3"], "r_loops": 1, "rows": 14697, "r_rows": 15000, "r_table_time_ms": 654.9452669, "r_other_time_ms": 13.68485618, "filtered": 20.41232872, "r_filtered": 20, "attached_condition": "a.c2 = 'c2_01'" } } } | 11.0(-~11.4) MariaDB [test]> analyze format=json SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3; { "query_optimization": { "r_total_time_ms": 2.986958457 }, "query_block": { "select_id": 1, "cost": 4.465185951, "r_loops": 1, "r_total_time_ms": 103.0670346, "filesort": { "sort_key": "a.c3", "r_loops": 1, "r_total_time_ms": 0.163121247, "r_used_priority_queue": false, "r_output_rows": 201, "r_buffer_size": "4Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "possible_keys": ["indx01"], "loops": 1, "r_loops": 1, "rows": 14700, "r_rows": 15000, "cost": 2.4890156, "r_table_time_ms": 84.82377979, "r_other_time_ms": 17.77049086, "r_engine_stats": { "pages_accessed": 59 }, "filtered": 20.40816307, "r_filtered": 20, "attached_condition": "a.c2 = 'c2_01'" } } ] } } } } 10.4: s| SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3 | { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select sql_no_cache a.c3 AS c3,sum(a.c7) AS `SUM(a.c7)`,count(a.c1) AS `COUNT(a.c1)` from tab1 a where a.c2 = 'c2_01' group by a.c3" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "a.c2 = 'c2_01'", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('c2_01', a.c2)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('c2_01', a.c2)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('c2_01', a.c2)" } ] } }, { "table_dependencies": [ { "table": "a", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "a", "field": "c2", "equals": "'c2_01'", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "a", "range_analysis": { "table_scan": { "rows": 14697, "cost": 3038.5 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "indx01", "usable": true, "key_parts": ["c2", "c7", "c1"] }, { "index": "indx02", "usable": true, "key_parts": ["c3", "c1", "c2"] }, { "index": "indx03", "usable": false, "cause": "not applicable" }, { "index": "indx04", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not applicable aggregate function" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "indx01", "ranges": ["(c2_01) <= (c2) <= (c2_01)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3000, "cost": 3783.4, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] } } }, { "selectivity_for_indexes": [ { "index_name": "indx01", "selectivity_from_index": 0.2041 } ], "selectivity_for_columns": [], "cond_selectivity": 0.2041 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "indx01", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 3000, "cost": 292, "chosen": true }, { "access_type": "scan", "resulting_rows": 3000, "cost": 2436.4, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 3000, "cost": 292, "uses_join_buffering": false } }, "rows_for_plan": 3000, "cost_for_plan": 892, "estimated_join_cardinality": 3000 } ] }, { "best_join_order": ["a"] }, { "attaching_conditions_to_tables": { "original_condition": "a.c2 = 'c2_01'", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "a", "attached": "a.c2 = 'c2_01'" } ] } }, { "test_if_skip_sort_order": [ { "reconsidering_access_paths_for_index_ordering": { "clause": "GROUP BY", "fanout": 1, "read_time": 292, "table": "a", "rows_estimation": 3000, "possible_keys": [ { "index": "PRIMARY", "can_resolve_order": false, "cause": "not usable index for the query" }, { "index": "indx01", "can_resolve_order": false, "cause": "not usable index for the query" }, { "index": "indx02", "can_resolve_order": true, "usable": false }, { "index": "indx03", "can_resolve_order": false, "cause": "not usable index for the query" }, { "index": "indx04", "can_resolve_order": false, "cause": "not usable index for the query" } ] } } ] } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 10.5 (-10.11): SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3 | { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select sql_no_cache a.c3 AS c3,sum(a.c7) AS `SUM(a.c7)`,count(a.c1) AS `COUNT(a.c1)` from tab1 a where a.c2 = 'c2_01' group by a.c3" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "a.c2 = 'c2_01'", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('c2_01', a.c2)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('c2_01', a.c2)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('c2_01', a.c2)" } ] } }, { "table_dependencies": [ { "table": "a", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "a", "field": "c2", "equals": "'c2_01'", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "a", "range_analysis": { "table_scan": { "rows": 14697, "cost": 3038.4 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "indx01", "usable": true, "key_parts": ["c2", "c7", "c1"] }, { "index": "indx02", "usable": true, "key_parts": ["c3", "c1", "c2"] }, { "index": "indx03", "usable": false, "cause": "not applicable" }, { "index": "indx04", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "indx01", "ranges": ["(c2_01) <= (c2) <= (c2_01)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3000, "cost": 3631.392788, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not applicable aggregate function" } } }, { "selectivity_for_indexes": [ { "index_name": "indx01", "selectivity_from_index": 0.20412329 } ], "selectivity_for_columns": [], "cond_selectivity": 0.20412329 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "indx01", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 3000, "cost": 3031.372788, "chosen": true }, { "access_type": "scan", "resulting_rows": 3000, "cost": 2436.4, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", "records": 3000, "cost": 2436.4, "uses_join_buffering": false } }, "rows_for_plan": 3000, "cost_for_plan": 3036.4, "cost_for_sorting": 3000, "estimated_join_cardinality": 3000 } ] }, { "best_join_order": ["a"] }, { "attaching_conditions_to_tables": { "original_condition": "a.c2 = 'c2_01'", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "a", "attached": "a.c2 = 'c2_01'" } ] } }, { "test_if_skip_sort_order": [ { "reconsidering_access_paths_for_index_ordering": { "clause": "GROUP BY", "fanout": 1, "read_time": 2436.401, "table": "a", "rows_estimation": 3000, "possible_keys": [ { "index": "PRIMARY", "can_resolve_order": false, "cause": "not usable index for the query" }, { "index": "indx01", "can_resolve_order": false, "cause": "not usable index for the query" }, { "index": "indx02", "can_resolve_order": true, "updated_limit": 14697, "index_scan_time": 14697, "records": 14697, "chosen": true }, { "index": "indx03", "can_resolve_order": false, "cause": "not usable index for the query" }, { "index": "indx04", "can_resolve_order": false, "cause": "not usable index for the query" } ] } } ] } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] } 11.0 (-11.4) SELECT sql_no_cache a.c3, SUM(a.c7), COUNT(a.c1)FROM tab1 a WHERE a.c2='c2_01' GROUP BY a.c3 | { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select sql_no_cache a.c3 AS c3,sum(a.c7) AS `SUM(a.c7)`,count(a.c1) AS `COUNT(a.c1)` from tab1 a where a.c2 = 'c2_01' group by a.c3" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "a.c2 = 'c2_01'", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "multiple equal('c2_01', a.c2)" }, { "transformation": "constant_propagation", "resulting_condition": "multiple equal('c2_01', a.c2)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "multiple equal('c2_01', a.c2)" } ] } }, { "table_dependencies": [ { "table": "a", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] } ] }, { "ref_optimizer_key_uses": [ { "table": "a", "index": "indx01", "field": "c2", "equals": "'c2_01'", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "a", "range_analysis": { "table_scan": { "rows": 14700, "cost": 2.4890156 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not applicable" }, { "index": "indx01", "usable": true, "key_parts": ["c2", "c7", "c1"] }, { "index": "indx02", "usable": true, "key_parts": ["c3", "c1", "c2"] }, { "index": "indx03", "usable": false, "cause": "not applicable" }, { "index": "indx04", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "indx01", "ranges": ["(c2_01) <= (c2) <= (c2_01)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3000, "cost": 3.08444424, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "cause": "too few roworder scans" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not applicable aggregate function" } } }, { "selectivity_for_indexes": [ { "index_name": "indx01", "selectivity_from_index": 0.204081633 } ], "selectivity_for_columns": [], "cond_selectivity": 0.204081633 } ] }, { "considered_execution_plans": [ { "plan_prefix": "", "get_costs_for_tables": [ { "best_access_path": { "table": "a", "plan_details": { "record_count": 1 }, "considered_access_paths": [ { "access_type": "ref", "index": "indx01", "used_range_estimates": true, "rows": 3000, "cost": 3.08365312, "chosen": true }, { "access_type": "scan", "rows": 14700, "rows_after_filter": 3000, "rows_out": 3000, "cost": 2.4890156, "index_only": false, "chosen": true, "use_tmp_table": true } ], "chosen_access_method": { "type": "scan", "rows_read": 3000, "rows_out": 3000, "cost": 2.4890156, "uses_join_buffering": false } } } ] }, { "plan_prefix": "", "table": "a", "rows_for_plan": 3000, "cost_for_plan": 2.4890156, "cost_for_sorting": 1.976170351 } ] }, { "best_join_order": ["a"], "rows": 3000, "cost": 4.465185951 }, { "substitute_best_equal": { "condition": "WHERE", "resulting_condition": "a.c2 = 'c2_01'" } }, { "attaching_conditions_to_tables": { "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "a", "attached_condition": "a.c2 = 'c2_01'" } ] } }, { "make_join_readinfo": [] }, { "test_if_skip_sort_order": [ { "reconsidering_access_paths_for_index_ordering": { "clause": "GROUP BY", "table": "a", "rows_estimation": 3000, "filesort_cost": 0.356339442, "read_cost": 2.845355042, "filesort_type": "merge_sort with addon fields", "fanout": 1, "possible_keys": [ { "index": "PRIMARY", "can_resolve_order": false, "cause": "not usable index for the query" }, { "index": "indx01", "can_resolve_order": false, "cause": "not usable index for the query" }, { "index": "indx02", "can_resolve_order": true, "direction": 1, "rows_to_examine": 14700, "range_scan": false, "scan_cost": 14.74536712, "usable": false, "cause": "cost" }, { "index": "indx03", "can_resolve_order": false, "cause": "not usable index for the query" }, { "index": "indx04", "can_resolve_order": false, "cause": "not usable index for the query" } ] } } ] } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] }
            monty Michael Widenius added a comment - - edited

            Quick analyze of what is different in 10.4 and 10.6 (based on the optimizer traces and comparing source code):

            In MariaDB 10.4:

                               "range_scan_alternatives": [
                                  {
                                    "index": "indx01",
                                    "ranges": ["(c2_01) <= (c2) <= (c2_01)"],
                                    "rowid_ordered": false,
                                    "using_mrr": false,
                                    "index_only": false,
                                    "rows": 3000,
                                    "cost": 3631.392788,
                                    "chosen": false,
                                    "cause": "cost"
                                  }
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 3000,
                                "cost": 2436.4,
                                "uses_join_buffering": false
                              }
                            },
                              "considered_access_paths": [
                                {
                                  "access_type": "ref",
                                  "index": "indx01",
                                  "used_range_estimates": true,
                                  "rowid_filter_skipped": "worst/max seeks clipping",
                                  "rows": 3000,
            >>>>>>>       "cost": 292,
                                  "chosen": true
                                },
            

            The cost change above happens because 10.4 limits the cost of 'ref' to 1/10 of
            the calculated cost. (worst_seeks optimization). This cost is so small that
            ORDER BY optimization later concludes that there is no point in doing an
            index scan to eliminate the GROUP BY and thus keeps the 'ref' usage.
            Note that we do not worst_seek_optimization for covering indexes, which means that in 10.4
            a 'ref' trumps almost all cases of covering index and ranges!

                        if (table->covering_keys.is_set(key))
                          keyread_tmp=
                            tmp= table->file->keyread_time(key, 1, (ha_rows) tmp);
                        else
                        {
                          keyread_tmp= table->file->keyread_time(key, 1, (ha_rows) tmp);
                          tmp= table->file->read_time(key, 1,
            >>>>>>>>>>>                                        (ha_rows) MY_MIN(tmp,s->worst_seeks));
            

            In MariaDB 10.6:

             
                             "analyzing_range_alternatives": {
                                "range_scan_alternatives": [
                                  {
                                    "index": "indx01",
                                    "ranges": ["(c2_01) <= (c2) <= (c2_01)"],
                                    "rowid_ordered": false,
                                    "using_mrr": false,
                                    "index_only": false,
                                    "rows": 3000,
                                    "cost": 3631.392788,
                                    "chosen": false,
                                    "cause": "cost"
                                  }
                                ],
             ...
                              "chosen_access_method": {
                                "type": "scan",
                                "records": 3000,
                                "cost": 2436.4,
                                "uses_join_buffering": false
                              }
            

            All cost are the same in 10.4 and 10.6, except for worst_seeks_optimization.
            In 10.5/10.6 we did a change so that if a cost comes from the range optimizer, as in the given
            query, then we trust the cost of the range and do not divide it by 10!
            This causes 10.6 to use a table scan instead an index scan (which should be ok in this case as in InnoDB table scans are in most cases at least 5x faster than non covering index scans)

            In 10.6, the decision of using index02 comes from the
            test_if_skip_sort_order":

             
                               {
                                  "index": "indx02",
                                  "can_resolve_order": true,
                                  "updated_limit": 14697,
                                  "index_scan_time": 14697,
                                  "records": 14697,
                                  "chosen": true
                                },
            

            The cost of index_scan_time is quite high and I do not know why this is used. I will now concentrate my time on trying to do some kind of cost adjustment in 10.6 for GROUP BY to not use
            the index in cases like this.

            monty Michael Widenius added a comment - - edited Quick analyze of what is different in 10.4 and 10.6 (based on the optimizer traces and comparing source code): In MariaDB 10.4: "range_scan_alternatives": [ { "index": "indx01", "ranges": ["(c2_01) <= (c2) <= (c2_01)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3000, "cost": 3631.392788, "chosen": false, "cause": "cost" } "chosen_access_method": { "type": "scan", "records": 3000, "cost": 2436.4, "uses_join_buffering": false } }, "considered_access_paths": [ { "access_type": "ref", "index": "indx01", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 3000, >>>>>>> "cost": 292, "chosen": true }, The cost change above happens because 10.4 limits the cost of 'ref' to 1/10 of the calculated cost. (worst_seeks optimization). This cost is so small that ORDER BY optimization later concludes that there is no point in doing an index scan to eliminate the GROUP BY and thus keeps the 'ref' usage. Note that we do not worst_seek_optimization for covering indexes, which means that in 10.4 a 'ref' trumps almost all cases of covering index and ranges! if (table->covering_keys.is_set(key)) keyread_tmp= tmp= table->file->keyread_time(key, 1, (ha_rows) tmp); else { keyread_tmp= table->file->keyread_time(key, 1, (ha_rows) tmp); tmp= table->file->read_time(key, 1, >>>>>>>>>>> (ha_rows) MY_MIN(tmp,s->worst_seeks)); In MariaDB 10.6: "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "indx01", "ranges": ["(c2_01) <= (c2) <= (c2_01)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3000, "cost": 3631.392788, "chosen": false, "cause": "cost" } ], ... "chosen_access_method": { "type": "scan", "records": 3000, "cost": 2436.4, "uses_join_buffering": false } All cost are the same in 10.4 and 10.6, except for worst_seeks_optimization. In 10.5/10.6 we did a change so that if a cost comes from the range optimizer, as in the given query, then we trust the cost of the range and do not divide it by 10! This causes 10.6 to use a table scan instead an index scan (which should be ok in this case as in InnoDB table scans are in most cases at least 5x faster than non covering index scans) In 10.6, the decision of using index02 comes from the test_if_skip_sort_order": { "index": "indx02", "can_resolve_order": true, "updated_limit": 14697, "index_scan_time": 14697, "records": 14697, "chosen": true }, The cost of index_scan_time is quite high and I do not know why this is used. I will now concentrate my time on trying to do some kind of cost adjustment in 10.6 for GROUP BY to not use the index in cases like this.

            Background:
            In MariaDB up to 10.11, the test_if_cheaper_ordering() code (that tries
            to optimizer how GROUP BY is executed) assumes that if a table scan is used
            then if there is any index usable by GROUP BY it will be used.

            The reason MySQL 10.4 provides a better plan is because of two differences:

            • Plans using 'ref' has a cost of 1/10 of what it should be (as a protection
              against table scans). This is why 'ref' is used in 10.4 and not in 10.5
            • When 'ref' is used, then GROUP BY will not use an index for GROUP BY.

            In MariaDB the chosen plan is a table scan (as it calculated to be faster)
            but as 'ref' is not used, the test_if_cheaper_ordering() optimizer decide to
            use an index for GROUP BY, which has bad performance.

            Code changes suggested to be done in 10.6:

            • Add a fix that only corrects GROUP BY handling in test_if_cheaper_ordering()
              by removing out the the unconditional 'if' and making the choise cost
              based.
            • We only execute this code if "optimizer_adjust_secondary_key_costs & 4" is
              set (we are reusing an existing variable that will be removed in 11.0
              anyway).

            This means that we have to fix the following cost estimate for
            index_scan_cost to be relevant:

            index_scan_time= select_limit/rec_per_key *
            MY_MIN(rec_per_key, table->file->scan_time());

            • Instead we should us standard functions for key range cost
            • We already have cost handing in multi_range_read() and
              cost_for_index_read() that handles this properly.
            • Limits must be handled properly (if possible taken selectivity into account)
            monty Michael Widenius added a comment - Background: In MariaDB up to 10.11, the test_if_cheaper_ordering() code (that tries to optimizer how GROUP BY is executed) assumes that if a table scan is used then if there is any index usable by GROUP BY it will be used. The reason MySQL 10.4 provides a better plan is because of two differences: Plans using 'ref' has a cost of 1/10 of what it should be (as a protection against table scans). This is why 'ref' is used in 10.4 and not in 10.5 When 'ref' is used, then GROUP BY will not use an index for GROUP BY. In MariaDB the chosen plan is a table scan (as it calculated to be faster) but as 'ref' is not used, the test_if_cheaper_ordering() optimizer decide to use an index for GROUP BY, which has bad performance. Code changes suggested to be done in 10.6: Add a fix that only corrects GROUP BY handling in test_if_cheaper_ordering() by removing out the the unconditional 'if' and making the choise cost based. We only execute this code if "optimizer_adjust_secondary_key_costs & 4" is set (we are reusing an existing variable that will be removed in 11.0 anyway). This means that we have to fix the following cost estimate for index_scan_cost to be relevant: index_scan_time= select_limit/rec_per_key * MY_MIN(rec_per_key, table->file->scan_time()); Instead we should us standard functions for key range cost We already have cost handing in multi_range_read() and cost_for_index_read() that handles this properly. Limits must be handled properly (if possible taken selectivity into account)

            Have a patch, will just do some testing and then push to bb-10.6-monty for review

            monty Michael Widenius added a comment - Have a patch, will just do some testing and then push to bb-10.6-monty for review

            Pushed to bb-10.6-monty branch

            monty Michael Widenius added a comment - Pushed to bb-10.6-monty branch
            monty Michael Widenius added a comment - - edited

            Fix pushed to 10.6 tree

            How to use the "fixed version":

            • Without any changes in configuration or using the optimizer_adjust_secondary_key_costs things will work 'exactly as before'.
              (This is to ensure that we do not break any existing applications.

            The variable optimizer_adjust_secondary_key_costs can be set to a combination of the following values separated by a ',':

            • adjust_secondary_key_cost : Update secondary key costs for ranges to be at least 5x of clustered primary key costs.
            • disable_max_seek: Disable 'max_seek optimization' for secondary keys and slight adjustment of filter cost.
            • disable_forced_index_in_group_by Disable automatic forced index in GROUP BY and make GROUP BY cost based instead of rule based.
            • ALL Sets all of the above values.
            monty Michael Widenius added a comment - - edited Fix pushed to 10.6 tree How to use the "fixed version": Without any changes in configuration or using the optimizer_adjust_secondary_key_costs things will work 'exactly as before'. (This is to ensure that we do not break any existing applications. The variable optimizer_adjust_secondary_key_costs can be set to a combination of the following values separated by a ',': adjust_secondary_key_cost : Update secondary key costs for ranges to be at least 5x of clustered primary key costs. disable_max_seek: Disable 'max_seek optimization' for secondary keys and slight adjustment of filter cost. disable_forced_index_in_group_by Disable automatic forced index in GROUP BY and make GROUP BY cost based instead of rule based. ALL Sets all of the above values.

            The patch could not easily be done in 10.5 as it lacks the
            optimizer_adjust_secondary_key_costs variable (and related code) that was added to 10.6.
            In short, fixing this in 10.5 is not really feasible/easy.
            Better to get users to upgrade to 10.6 or 11.4. 11.4 has a better optimizer and does not need the optimizer_adjust_secondary_key_costs variable.

            monty Michael Widenius added a comment - The patch could not easily be done in 10.5 as it lacks the optimizer_adjust_secondary_key_costs variable (and related code) that was added to 10.6. In short, fixing this in 10.5 is not really feasible/easy. Better to get users to upgrade to 10.6 or 11.4. 11.4 has a better optimizer and does not need the optimizer_adjust_secondary_key_costs variable.

            People

              monty Michael Widenius
              susmeet.khaire Susmeet Khaire
              Votes:
              1 Vote for this issue
              Watchers:
              10 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.