|
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": []
|
}
|
}
|
]
|
}
|
|
MDEV-33306.test
