[MDEV-27200] EXPLAIN plan 'key' changed for same query after upgrade from 10.3 to 10.4 in same dataset Created: 2021-12-08  Updated: 2022-04-05  Resolved: 2022-03-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.19
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Koustuv Chatterjee Assignee: Unassigned
Resolution: Incomplete Votes: 2
Labels: regression
Environment:

CentOS


 Description   

After making optimizer_use_condition_selectivity=1 from 4 and disabling the below optimizer_switch options that weren't enabled in 10.3.21 we still get a different 'key' selected. As a result query that took ~0.5 sec in 10.3 takes ~1.5 min in 10.4

  • optimize_join_buffer_size
  • condition_pushdown_for_subquery
  • rowid_filter
  • condition_pushdown_from_having

FORCE INDEX (key used in 10.3) however worked in 10.4 and execution time was same like 10.3

EXPLAIN IN 10.4

id: 1
 
select_type: SIMPLE
 
table: table_name
 
type: range
 
possible_keys: index_table_col1, index_table_col2, ... ,index_table_col6
 
key: index_table_col6
 
key_len: 4
 
ref: NULL
 
rows: 80196
 
Extra: Using index condition; Using where
 
1 row in set (0.003 sec)

Same Query in 10.3

id: 1
 
select_type: SIMPLE
 
table: table_name
 
type: range
 
possible_keys:  index_table_col1, index_table_col2, ... ,index_table_col6
 
key: index_table_col5
 
key_len: 7
 
ref: NULL
 
rows: 65841
 
Extra: Using index condition; Using where
 
1 row in set (0.017 sec)



 Comments   
Comment by Alasdair Haswell [ 2022-03-22 ]

I am not able to provide a reproducible test case with the initial dump provided by customer, we asked for a valid dump again through (CS0389159). Waiting for inputs from customer.

Generated at Thu Feb 08 09:51:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.