query_plan_* session settings

These settings are available in system.settings and are autogenerated from source.

query_plan_aggregation_bucket_top_k

Toggles a query-plan-level optimization which, when a final aggregation feeds ORDER BY over the aggregation’s outputs with LIMIT n and the plan proves the per-bucket selection exact, materializes only each two-level bucket’s best n groups in that order during the aggregation’s final conversion. The result is exact: a group outside its own bucket’s best n has at least n groups ahead of it globally, so it cannot be in the global top n. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_aggregation_in_order

Toggles the aggregation in-order query-plan-level optimization. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_convert_any_join_to_semi_or_anti_join

Allow to convert ANY JOIN to SEMI or ANTI JOIN if filter after JOIN always evaluates to false for not-matched or matched rows

query_plan_convert_join_to_in

Allow to convert JOIN to subquery with IN if output columns tied to only left table. May cause wrong results with non-ANY JOINs (e.g. ALL JOINs which is the default).

query_plan_convert_outer_join_to_inner_join

Allow to convert OUTER JOIN to INNER JOIN if filter after JOIN always filters default values

query_plan_direct_read_from_text_index

Allow to perform full text search filtering using only the inverted text index in query plan.

query_plan_display_internal_aliases

Show internal aliases (such as __table1) in EXPLAIN PLAN instead of those specified in the original query.

query_plan_enable_multithreading_after_window_functions

Enable multithreading after evaluating window functions to allow parallel stream processing

query_plan_enable_optimizations

Toggles query optimization at the query plan level.

Possible values:

• 0 - Disable all optimizations at the query plan level
• 1 - Enable optimizations at the query plan level (but individual optimizations may still be disabled via their individual settings)

query_plan_execute_functions_after_sorting

Toggles a query-plan-level optimization which moves expressions after sorting steps. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_filter_push_down

Toggles a query-plan-level optimization which moves filters down in the execution plan. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_fuse_filter_into_array_join

Toggles a query-plan-level optimization which fuses a filter on ARRAY JOINed element columns into the ARRAY JOIN step, filtering the arrays in element space before expansion so that filtered-out elements are never expanded or replicated. Only takes effect if setting query_plan_enable_optimizations is 1.

query_plan_join_shard_by_pk_ranges

Apply sharding for JOIN if join keys contain a prefix of PRIMARY KEY for both tables. Supported for hash, parallel_hash, full_sorting_merge and parallel_full_sorting_merge algorithms. Usually does not speed up queries but may lower memory consumption.

query_plan_join_swap_table

Determine which side of the join should be the build table (also called inner, the one inserted into the hash table for a hash join) in the query plan. This setting is supported only for ALL join strictness with the JOIN ON clause. Possible values are:

• ‘auto’: Let the planner decide which table to use as the build table.
• ‘false’: Never swap tables (the right table is the build table).
• ‘true’: Always swap tables (the left table is the build table).

query_plan_lift_up_array_join

Toggles a query-plan-level optimization which moves ARRAY JOINs up in the execution plan. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_lift_up_union

Toggles a query-plan-level optimization which moves larger subtrees of the query plan into union to enable further optimizations. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_lower_array_join_function

Toggles a query-plan-level optimization which lowers an arrayJoin function inside an expression into a real ARRAY JOIN step, so it goes through the same execution machinery as the ARRAY JOIN clause (lazy replication and filter fusion). Only takes effect if setting query_plan_enable_optimizations is 1.

query_plan_max_limit_for_join_lazy_indexing

Control maximum limit value that allows to use query plan for lazy indexing optimization in JOIN. If zero, there is no limit.

query_plan_max_limit_for_lazy_materialization

Control maximum limit value that allows to use query plan for lazy materialization optimization. If zero, there is no limit.

query_plan_max_limit_for_top_k_optimization

Control maximum limit value that allows to evaluate query plan for TopK optimization by using minmax skip index and dynamic threshold filtering. If zero, there is no limit.

This setting also controls the behavior of enable_group_by_top_k_optimization.

query_plan_max_optimizations_to_apply

Limits the total number of optimizations applied to query plan, see setting query_plan_enable_optimizations. Useful to avoid long optimization times for complex queries. In the EXPLAIN PLAN query, stop applying optimizations after this limit is reached and return the plan as is. For regular query execution if the actual number of optimizations exceeds this setting, an exception is thrown.

query_plan_max_set_size_for_projection_match

Maximum number of rows in an IN-clause set for which the projection matcher computes and compares content hashes when deciding whether two sets are equal. Sets larger than this are treated as non-matching and skip the projection. Zero disables content-hash comparison entirely: a projection match never succeeds for nodes containing IN-clause sets.

Used by the aggregate projection matcher (and any future projection matcher that needs to compare IN-clause sets). Computing the content hash is O(N log N) in the number of set elements; this setting bounds the cost paid during planning when many IN-clauses appear in the query or the projection.

query_plan_max_step_description_length

Maximum length of step description in EXPLAIN PLAN.

query_plan_merge_expression_into_join

Allow to merge expressions into JOIN step during join reordering optimization.

query_plan_merge_expressions

Toggles a query-plan-level optimization which merges consecutive filters. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_merge_filter_into_join_condition

Allow to merge filter into JOIN condition and convert CROSS JOIN to INNER.

query_plan_merge_filters

Allow to merge filters in the query plan.

query_plan_min_columns_for_join_lazy_indexing

Control the minimum number of payload columns from the left side required for enabling lazy indexing optimization in JOIN. 0 means the optimization is disabled.

query_plan_optimize_count_from_text_index

Allow to answer SELECT count() ... WHERE <text search predicate> directly from the text index posting-list cardinalities, without materializing the matching rows. Only takes effect when query_plan_direct_read_from_text_index is enabled.

query_plan_optimize_join_order_algorithm

Specifies which JOIN order algorithms to attempt during query plan optimization. The following algorithms are available:

• ‘greedy’ - basic greedy algorithm - works fast but might not produce the best join order
• ‘dpsize’ - implements DPsize algorithm currently only for Inner joins - considers all possible join orders and finds the most optimal one but might be slow for queries with many tables and join predicates.
• ‘dpsub’ - implements DPsub algorithm which supports both inner and non-inner joins - considers all possible join orders and finds the most optimal one but might be slow for queries with many tables and join predicates.
• ‘dphyp’ - implements DPhyp (Dynamic Programming via Hypergraph Partitioning) algorithm currently only for inner joins - explores the same search space as dpsize but enumerates only connected subgraph pairs, which generates fewer intermediate joins on sparse join graphs, at the cost of not considering cross products Multiple algorithms can be specified as a comma-separated list, e.g. dphyp,greedy. They are tried in order; if an algorithm cannot handle the query (e.g. due to outer joins or disconnected components), the next one is used as a fallback.

query_plan_optimize_join_order_limit

Optimize the order of joins within the same subquery. Currently only supported for very limited cases. Value is the maximum number of tables to optimize.

query_plan_optimize_join_order_max_searched_plans

Maximum number of partial plans the join order optimizer may enumerate before giving up and falling back to the next algorithm in query_plan_optimize_join_order_algorithm. This bounds optimization time deterministically (independent of wall-clock) on dense join graphs such as cliques or stars, where the search space grows exponentially. Set to 0 to disable the limit. Has no effect on the default query_plan_optimize_join_order_limit, where the search always stays well below this bound.

query_plan_optimize_join_order_randomize

When non-zero, the join order optimizer uses randomly generated cardinalities and NDVs instead of real statistics. When set to 1, a random seed is generated, when set to a value > 1, that value is used as the seed directly. This is intended for testing to find errors caused by different join orderings.

query_plan_optimize_lazy_final

Optimize reading with FINAL from ReplacingMergeTree by building a set of primary keys and using it for index analysis.

query_plan_optimize_lazy_materialization

Use query plan for lazy materialization optimization.

query_plan_optimize_lazy_materialization_for_file

Use lazy materialization optimization for reading local Parquet files with the file table function and the File table engine: for ORDER BY ... LIMIT n queries, the columns that are not needed for sorting and filtering are read only for the n rows that survive the LIMIT. Takes effect only if query_plan_optimize_lazy_materialization is enabled.

query_plan_optimize_lazy_materialization_for_object_storage

Use lazy materialization optimization for reading Parquet files from object storage (including Iceberg tables): for ORDER BY ... LIMIT n queries, the columns that are not needed for sorting and filtering are read only for the n rows that survive the LIMIT. Takes effect only if query_plan_optimize_lazy_materialization is enabled.

query_plan_optimize_prewhere

Allow to push down filter to PREWHERE expression for supported storages

query_plan_push_down_limit

Toggles a query-plan-level optimization which moves LIMITs down in the execution plan. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_push_down_volume_reducing_functions

Toggles a query-plan-level optimization which moves volume-reducing functions (length, lengthUTF8, empty, notEmpty) down in the execution plan, below Sorting and Filter steps. The fixed-size result replaces the wide String / FixedString argument, so the argument is no longer carried through those steps. The rewrite is only applied when the argument column is not needed above the step it is pushed below.

Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_push_limit_by_into_sort

Toggles a query-plan-level optimization for ORDER BY ... LIMIT BY queries. When LIMIT BY columns are a prefix of the ORDER BY clause, each parallel sorted stream applies LIMIT BY before the streams are merged into one, reducing rows processed by the final merge and later pipeline stages. Speeds up queries where LIMIT BY discards a large fraction of rows.

Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_read_in_order

Toggles the read in-order optimization query-plan-level optimization. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_read_in_order_through_join

Keep reading in order from the left table in JOIN operations, which can be utilized by subsequent steps.

query_plan_remove_redundant_distinct

Toggles a query-plan-level optimization which removes redundant DISTINCT steps. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_remove_redundant_sorting

Toggles a query-plan-level optimization which removes redundant sorting steps, e.g. in subqueries. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_remove_unused_columns

Toggles a query-plan-level optimization which tries to remove unused columns (both input and output columns) from query plan steps. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_reuse_storage_ordering_for_window_functions

Aliases: optimize_read_in_window_order

Toggles a query-plan-level optimization which uses storage sorting when sorting for window functions. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_short_circuit_constant_false_join

Short-circuit a JOIN whose ON condition folds to a constant false by replacing each input side that cannot contribute a row (both sides for INNER/CROSS/SEMI, the non-preserved side for LEFT/RIGHT) with an empty source, so the non-contributing side is not read. Applies to the analyzer (enable_analyzer = 1) and to non-distributed plans.

query_plan_split_filter

Toggles a query-plan-level optimization which splits filters into expressions. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_text_index_add_hint

Allow to add hint (additional predicate) for filtering built from the inverted text index in query plan.

query_plan_top_k_through_join

Toggles a query-plan-level optimization which pushes ORDER BY ... LIMIT n down through a join when the sort key only references columns from the side preserved by the join (LEFT/RIGHT). Restricts how many rows the preserved-side input must produce before joining. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable

query_plan_try_use_vector_search

Toggles a query-plan-level optimization which tries to use the vector similarity index. Only takes effect if setting query_plan_enable_optimizations is 1.

Possible values:

• 0 - Disable
• 1 - Enable