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

Optimizer does not use the best index for range access

    XMLWordPrintable

    Details

      Description

      Consider the following test on a table with some data in it:

      MariaDB [(none)]> select version();
      +-------------------------------------+
      | version() |
      +-------------------------------------+
      | 10.6.8-MariaDB-1:10.6.8+maria~focal |
      +-------------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [(none)]> use data_pool;
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Database changed
      MariaDB [data_pool]> show create table action_integer\G
      *************************** 1. row ***************************
      Table: action_integer
      Create Table: CREATE TABLE `action_integer` (
      `id` bigint(20) NOT NULL,
      `cr_date` datetime(6) DEFAULT NULL,
      `element_date` datetime(6) DEFAULT NULL,
      `element_id` bigint(20) DEFAULT NULL,
      `event_date` datetime(6) DEFAULT NULL,
      `parent_event` bigint(20) DEFAULT NULL,
      `parent_type` varchar(1) DEFAULT NULL,
      `source_device` varchar(16) DEFAULT NULL,
      `source_name_id` varchar(16) DEFAULT NULL,
      `source_type` varchar(16) DEFAULT NULL,
      `type` smallint(6) NOT NULL,
      `e_integer` bigint(20) DEFAULT NULL,
      PRIMARY KEY (`id`,`type`),
      KEY `element_id_event_date_index` (`element_id`,`event_date`),
      KEY `type_e_integer_index` (`type`,`e_integer`),
      KEY `parent_event_event_date_index` (`parent_event`,`event_date`),
      KEY `type_element_id_event_date_index` (`type`,`element_id`,`event_date`),
      KEY `event_date_type_e_integer_index` (`event_date`,`type`,`e_integer`),
      KEY `element_id_index` (`element_id`),
      KEY `event_date_index` (`event_date`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
      PARTITION BY HASH (`type`)
      PARTITIONS 40
      1 row in set (0.000 sec)
       
      MariaDB [data_pool]> set session optimizer_switch='rowid_filter=OFF';
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [data_pool]> set session optimizer_use_condition_selectivity = 1;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [data_pool]> show session variables like 'optimizer%';
      +--------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Variable_name | Value |
      +--------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | optimizer_max_sel_arg_weight | 32000 |
      | optimizer_prune_level | 1 |
      | optimizer_search_depth | 62 |
      | optimizer_selectivity_sampling_limit | 100 |
      | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=off,condition_pushdown_from_having=on,not_null_range_scan=off |
      | optimizer_trace | enabled=off |
      | optimizer_trace_max_mem_size | 1048576 |
      | optimizer_use_condition_selectivity | 1 |
      +--------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      8 rows in set (0.001 sec)
       
      MariaDB [data_pool]> analyze format = json select element_id from action_integer
      -> where event_date
      -> between '2022-01-02 11:00:00' and '2022-01-02 11:10:00'
      -> and type = 25
      -> and e_integer >= 3000
      -> order by event_date desc;
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | ANALYZE |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | {
      "query_block": {
      "select_id": 1,
      "r_loops": 1,
      "r_total_time_ms": 0.748598657,
      "table": {
      "table_name": "action_integer",
      "partitions": ["p25"],
      "access_type": "range",
      "possible_keys": [
      "type_e_integer_index",
      "type_element_id_event_date_index",
      "event_date_type_e_integer_index",
      "event_date_index"
      ],
      "key": "event_date_index",
      "key_length": "9",
      "used_key_parts": ["event_date"],
      "r_loops": 1,
      "rows": 1,
      "r_rows": 0,
      "r_table_time_ms": 0.709776119,
      "r_other_time_ms": 0.021946347,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "action_integer.`type` = 25 and action_integer.event_date between '2022-01-02 11:00:00.000000' and '2022-01-02 11:10:00.000000' and action_integer.e_integer >= 3000"
      }
      }
      } |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.017 sec)
      

      Now compare to this result with other index FORCEd, in the same session:

      MariaDB [data_pool]> analyze format = json select element_id from action_integer
      -> FORCE INDEX (event_date_type_e_integer_index)
      -> where event_date
      -> between '2022-01-02 11:00:00' and '2022-01-02 11:10:00'
      -> and type = 25
      -> and e_integer >= 3000
      -> order by event_date desc;
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | ANALYZE |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | {
      "query_block": {
      "select_id": 1,
      "r_loops": 1,
      "r_total_time_ms": 0.053786246,
      "table": {
      "table_name": "action_integer",
      "partitions": ["p25"],
      "access_type": "range",
      "possible_keys": ["event_date_type_e_integer_index"],
      "key": "event_date_type_e_integer_index",
      "key_length": "20",
      "used_key_parts": ["event_date", "type", "e_integer"],
      "r_loops": 1,
      "rows": 1,
      "r_rows": 0,
      "r_table_time_ms": 0.026492428,
      "r_other_time_ms": 0.014467001,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "action_integer.`type` = 25 and action_integer.event_date between '2022-01-02 11:00:00.000000' and '2022-01-02 11:10:00.000000' and action_integer.e_integer >= 3000"
      }
      }
      } |
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
      

      Note more key parts used and faster execution. Here is the optimizer trace:

      MariaDB [data_pool]> set session optimizer_trace='enabled=on';
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [data_pool]> analyze format = json select element_id from action_integer
      -> where event_date
      -> between '2022-01-02 11:00:00' and '2022-01-02 11:10:00'
      -> and type = 25
      -> and e_integer >= 3000
      -> order by event_date desc;
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | ANALYZE |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | {
      "query_block": {
      "select_id": 1,
      "r_loops": 1,
      "r_total_time_ms": 0.074182264,
      "table": {
      "table_name": "action_integer",
      "partitions": ["p25"],
      "access_type": "range",
      "possible_keys": [
      "type_e_integer_index",
      "type_element_id_event_date_index",
      "event_date_type_e_integer_index",
      "event_date_index"
      ],
      "key": "event_date_index",
      "key_length": "9",
      "used_key_parts": ["event_date"],
      "r_loops": 1,
      "rows": 1,
      "r_rows": 0,
      "r_table_time_ms": 0.034520477,
      "r_other_time_ms": 0.020919179,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "action_integer.`type` = 25 and action_integer.event_date between '2022-01-02 11:00:00.000000' and '2022-01-02 11:10:00.000000' and action_integer.e_integer >= 3000"
      }
      }
      } |
      +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.001 sec)
       
      MariaDB [data_pool]> select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
      *************************** 1. row ***************************
      QUERY: analyze format = json select element_id from action_integer
      where event_date
      between '2022-01-02 11:00:00' and '2022-01-02 11:10:00'
      and type = 25
      and e_integer >= 3000
      order by event_date desc
      TRACE: {
      "steps": [
      {
      "join_preparation": {
      "select_id": 1,
      "steps": [
      {
      "expanded_query": "select action_integer.element_id AS element_id from action_integer where action_integer.event_date between <cache>('2022-01-02 11:00:00') and <cache>('2022-01-02 11:10:00') and action_integer.`type` = 25 and action_integer.e_integer >= 3000 order by action_integer.event_date desc"
      }
      ]
      }
      },
      {
      "join_optimization": {
      "select_id": 1,
      "steps": [
      {
      "condition_processing": {
      "condition": "WHERE",
      "original_condition": "action_integer.event_date between <cache>('2022-01-02 11:00:00') and <cache>('2022-01-02 11:10:00') and action_integer.`type` = 25 and action_integer.e_integer >= 3000",
      "steps": [
      {
      "transformation": "equality_propagation",
      "resulting_condition": "action_integer.event_date between <cache>('2022-01-02 11:00:00') and <cache>('2022-01-02 11:10:00') and action_integer.e_integer >= 3000 and multiple equal(25, action_integer.`type`)"
      },
      {
      "transformation": "constant_propagation",
      "resulting_condition": "action_integer.event_date between <cache>('2022-01-02 11:00:00') and <cache>('2022-01-02 11:10:00') and action_integer.e_integer >= 3000 and multiple equal(25, action_integer.`type`)"
      },
      {
      "transformation": "trivial_condition_removal",
      "resulting_condition": "action_integer.event_date between <cache>('2022-01-02 11:00:00') and <cache>('2022-01-02 11:10:00') and action_integer.e_integer >= 3000 and multiple equal(25, action_integer.`type`)"
      }
      ]
      }
      },
      {
      "table_dependencies": [
      {
      "table": "action_integer",
      "row_may_be_null": false,
      "map_bit": 0,
      "depends_on_map_bits": []
      }
      ]
      },
      {
      "ref_optimizer_key_uses": [
      {
      "table": "action_integer",
      "field": "type",
      "equals": "25",
      "null_rejecting": false
      },
      {
      "table": "action_integer",
      "field": "type",
      "equals": "25",
      "null_rejecting": false
      }
      ]
      },
      {
      "rows_estimation": [
      {
      "table": "action_integer",
      "range_analysis": {
      "table_scan": {
      "rows": 23678726,
      "cost": 4940291.2
      },
      "potential_range_indexes": [
      {
      "index": "PRIMARY",
      "usable": false,
      "cause": "not applicable"
      },
      {
      "index": "element_id_event_date_index",
      "usable": false,
      "cause": "not applicable"
      },
      {
      "index": "type_e_integer_index",
      "usable": true,
      "key_parts": ["type", "e_integer"]
      },
      {
      "index": "parent_event_event_date_index",
      "usable": false,
      "cause": "not applicable"
      },
      {
      "index": "type_element_id_event_date_index",
      "usable": true,
      "key_parts": ["type", "element_id", "event_date"]
      },
      {
      "index": "event_date_type_e_integer_index",
      "usable": true,
      "key_parts": ["event_date", "type", "e_integer"]
      },
      {
      "index": "element_id_index",
      "usable": false,
      "cause": "not applicable"
      },
      {
      "index": "event_date_index",
      "usable": true,
      "key_parts": ["event_date"]
      }
      ],
      "setup_range_conditions": [],
      "analyzing_range_alternatives": {
      "range_scan_alternatives": [
      {
      "index": "type_e_integer_index",
      "ranges": ["(25,3000) <= (type,e_integer) <= (25)"],
      "rowid_ordered": false,
      "using_mrr": false,
      "index_only": false,
      "rows": 2005818,
      "cost": 2411243.9,
      "chosen": true
      },
      {
      "index": "type_element_id_event_date_index",
      "ranges": ["(25) <= (type) <= (25)"],
      "rowid_ordered": false,
      "using_mrr": false,
      "index_only": false,
      "rows": 11839363,
      "cost": 14242321.18,
      "chosen": false,
      "cause": "cost"
      },
      {
      "index": "event_date_type_e_integer_index",
      "ranges": [
      "(2022-01-02 11:00:00.000000,25,3000) <= (event_date,type,e_integer) <= (2022-01-02 11:10:00.000000,25)"
      ],
      "rowid_ordered": false,
      "using_mrr": false,
      "index_only": false,
      "rows": 1,
      "cost": 1.345366189,
      "chosen": true
      },
      {
      "index": "event_date_index",
      "ranges": [
      "(2022-01-02 11:00:00.000000) <= (event_date) <= (2022-01-02 11:10:00.000000)"
      ],
      "rowid_ordered": false,
      "using_mrr": false,
      "index_only": false,
      "rows": 1,
      "cost": 1.345231919,
      "chosen": true
      }
      ],
      "analyzing_roworder_intersect": {
      "cause": "too few roworder scans"
      },
      "analyzing_index_merge_union": []
      },
      "group_index_range": {
      "chosen": false,
      "cause": "no group by or distinct"
      },
      "chosen_range_access_summary": {
      "range_access_plan": {
      "type": "range_scan",
      "index": "event_date_index",
      "rows": 1,
      "ranges": [
      "(2022-01-02 11:00:00.000000) <= (event_date) <= (2022-01-02 11:10:00.000000)"
      ]
      },
      "rows_for_plan": 1,
      "cost_for_plan": 1.345231919,
      "chosen": true
      }
      }
      }
      ]
      },
      {
      "considered_execution_plans": [
      {
      "plan_prefix": [],
      "table": "action_integer",
      "best_access_path": {
      "considered_access_paths": [
      {
      "access_type": "ref",
      "index": "type_e_integer_index",
      "rows": 31368248,
      "cost": 622439.4657,
      "chosen": true
      },
      {
      "access_type": "ref",
      "index": "type_element_id_event_date_index",
      "used_range_estimates": true,
      "rows": 11839363,
      "cost": 11874448.56,
      "chosen": false,
      "cause": "cost"
      },
      {
      "access_type": "range",
      "resulting_rows": 1,
      "cost": 1.345231919,
      "chosen": true,
      "use_tmp_table": true
      }
      ],
      "chosen_access_method": {
      "type": "range",
      "records": 1,
      "cost": 1.345231919,
      "uses_join_buffering": false
      }
      },
      "rows_for_plan": 1,
      "cost_for_plan": 1.545231919,
      "cost_for_sorting": 1
      }
      ]
      },
      {
      "best_join_order": ["action_integer"]
      },
      {
      "substitute_best_equal": {
      "condition": "WHERE",
      "resulting_condition": "action_integer.`type` = 25 and action_integer.event_date between '2022-01-02 11:00:00.000000' and '2022-01-02 11:10:00.000000' and action_integer.e_integer >= 3000"
      }
      },
      {
      "attaching_conditions_to_tables": {
      "attached_conditions_computation": [],
      "attached_conditions_summary": [
      {
      "table": "action_integer",
      "attached": "action_integer.`type` = 25 and action_integer.event_date between '2022-01-02 11:00:00.000000' and '2022-01-02 11:10:00.000000' and action_integer.e_integer >= 3000"
      }
      ]
      }
      }
      ]
      }
      },
      {
      "join_execution": {
      "select_id": 1,
      "steps": []
      }
      }
      ]
      }
      MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
      INSUFFICIENT_PRIVILEGES: 0
      1 row in set (0.001 sec)
      

      It seems cost estimation is too pessimistic for the best index we have for this query.

      Note that with all default optimizer settings the plan is:

      MariaDB [data_pool]> explain select element_id from action_integer
      -> where event_date
      -> between '2022-01-02 11:00:00' and '2022-01-02 11:10:00'
      -> and type = 25
      -> and e_integer >= 3000
      -> order by event_date desc;
      +------+-------------+----------------+------------+--------------------------------------------------------------------------------------------------------+---------------------------------------+---------+-------+--------------+-------------------------------------------------+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
      +------+-------------+----------------+------------+--------------------------------------------------------------------------------------------------------+---------------------------------------+---------+-------+--------------+-------------------------------------------------+
      | 1 | SIMPLE | action_integer | ref|filter | type_e_integer_index,type_element_id_event_date_index,event_date_type_e_integer_index,event_date_index | type_e_integer_index|event_date_index | 2|9 | const | 2005818 (0%) | Using where; Using filesort; Using rowid filter |
      +------+-------------+----------------+------------+--------------------------------------------------------------------------------------------------------+---------------------------------------+---------+-------+--------------+-------------------------------------------------+
      1 row in set (0.001 sec)
      

      and it took around one minute to execute the query that way. So, I see two problems here:

      1. With default settings ref|filter access path is preferred, much worse.

      2. With non-default settings from the test case above it seems cost estimation for range access is somewhat wrong and we end up using suboptimal index for range access.

        Attachments

          Activity

            People

            Assignee:
            psergei Sergei Petrunia
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration

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