• Monty's avatar
    MDEV-33306 Optimizer choosing incorrect index in 10.6, 10.5 but not in 10.4 · 3907345e
    Monty authored
    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 10.5 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 phase decides (as ref is not usd) to use an index for GROUP BY,
    which has bad performance.
    
    Description of fix:
    - All new code is protected by the "optimizer_adjust_secondary_key_costs"
      variable, which is now a bit map, and is only executed if the option
      "disable_forced_index_in_group_by" set.
    - Corrects GROUP BY handling in test_if_cheaper_ordering() by making
      the choise of using and index with GROUP BY cost based instead of rule
      based.
    - Adds TIME_FOR_COMPARE to all costs, when using group by, to make
      read_time, index_scan_time and range_cost comparable.
    
    Other things:
    - Made optimizer_adjust_secondary_key_costs a bit map (compatible with old
      code).
    
    Notes:
    Current code ignores costs for the algorithm used when doing GROUP
    BY on the first table:
      - Create an in-memory temporary table for handling group by and doing a
        filesort of the result file
    We can probably in 10.6 continue to ignore this cost.
    
    This patch should NOT be merged to 11.0 series (not needed in 11.0).
    3907345e
sysvars_server_embedded.result 133 KB