[MCOL-3334] parameter optimizer_switch can not be changed Created: 2019-05-28  Updated: 2021-01-11  Resolved: 2021-01-11

Status: Closed
Project: MariaDB ColumnStore
Component/s: MariaDB Server
Affects Version/s: 1.1.6, 1.1.7, 1.2.3
Fix Version/s: 5.4.3

Type: Bug Priority: Major
Reporter: Maroš Lipták Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: optimizer
Environment:

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.



 Comments   
Comment by Maroš Lipták [ 2019-05-29 ]

It seems that parameter infinidb_vtable_mode = 0 resolves the problem described in this issue.

Anyway, I expected at least a warning from database when I executed
SET optimizer_switch='index_merge=on';
But the database informed me that the parameter was set and in reality it wasn't set. Usually in other situations (e.g. when I try to set invalid paramter value - SET optimizer_switch='index_merge=XXX') the database server at least inform me that the given value can not be applied. Therefore I would like to ask you to add a warning when user executes:
SET optimizer_switch='index_merge=on';
and infinidb_vtable_mode = 1 or infinidb_vtable_mode = 2.

Comment by Roman [ 2021-01-11 ]

This is a known issue for versions that uses a forked MDB server. Both 10.5 and 10.4 doesn't have this limitation.

Generated at Thu Feb 08 02:41:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.