Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
1.1.6, 1.1.7, 1.2.3
-
CentOS Linux, Debian Linux, Docker
Description
Parameter optimizer_switch can not be changed and it is not possible to enable/disable specific optimizations of query optimizer.
Testcase:
1) SET optimizer_switch='index_merge=on';
2) SHOW VARIABLES WHERE variable_name LIKE 'optimizer_switch';
Result: index_merge=off......
Outcome: optimization index_merge was not enabled.
It is also not possible to enable these optimizations as session variable. Testcase:
1) SET SESSION optimizer_switch='index_merge=on';
2) SHOW SESSION VARIABLES WHERE variable_name LIKE 'optimizer_switch';
Result: index_merge=off......
Outcome: again, optimization index_merge was not enabled.
I tried to configure optimizer_switch parameter also in my.cnf, but this modification was not reflected and all optimizations are always disabled:
_index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,extended_keys=off,exists_to_in=on,orderby_uses_equalities=off,condition_pushdown_for_derived=on,split_materialized=off
_
This bug is affecting me because I need to run some queries on InnoDB tables in MariaDB ColumnStore server. Without query optimizations I always have very silly query plan and complex queries with subselects do not use most of database indexes. When I executed the same query on standard MariaDB with query optimizations enabled in parameter optimizer_switch, all my queries were fast and indexes were used. My test query took 18 minutes on standard MariaDB and 50 hours on MariaDB ColumnStore on the same dataset.
This behavior affects only MariaDB ColumnStore. Standard MariaDB without ColumnStore is not affected and optimizer_switch works according to documentation.