optimize_* session settings

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

optimize_aggregators_of_group_by_keys

Eliminates min/max/any/anyLast aggregators of GROUP BY keys in SELECT sectio

optimize_append_index

Use constraints in order to append index condition. The default is false. Possible values:

true, false

optimize_arithmetic_operations_in_aggregate_functions

Move arithmetic operations out of aggregation functions

optimize_const_name_size

Replace with scalar and use hash as a name for large constants (size is estimated by the name length). Possible values:

positive integer - max length of the name,
0 — always,
negative integer - never.

optimize_count_from_files

Enables or disables the optimization of counting number of rows from files in different input formats. It applies to table functions/engines file/s3/url/hdfs/azureBlobStorage. Possible values:

0 — Optimization disabled.
1 — Optimization enabled.

optimize_dictget_tuple_element

Rewrite tupleElement(dictGet('dict', ('a', 'b', 'c'), key), 2) into dictGet('dict', 'b', key) to avoid fetching unnecessary dictionary attributes. Supports positional (.1, .2, …) and named (.b) access, and also applies to dictGetOrDefault when the default argument is a constant tuple or a tuple(...) of constants.

optimize_distinct_in_order

Enable DISTINCT optimization if some columns in DISTINCT form a prefix of sorting. For example, prefix of sorting key in merge tree or ORDER BY statement

optimize_distributed_group_by_sharding_key

Optimize GROUP BY sharding_key queries, by avoiding costly aggregation on the initiator server (which will reduce memory usage for the query on the initiator server). The following types of queries are supported (and all combinations of them):

SELECT DISTINCT [..., ]sharding_key[, ...] FROM dist
SELECT ... FROM dist GROUP BY sharding_key[, ...]
SELECT ... FROM dist GROUP BY sharding_key[, ...] ORDER BY x
SELECT ... FROM dist GROUP BY sharding_key[, ...] LIMIT 1
SELECT ... FROM dist GROUP BY sharding_key[, ...] LIMIT 1 BY x

The following types of queries are not supported (support for some of them may be added later):

SELECT ... GROUP BY sharding_key[, ...] WITH TOTALS
SELECT ... GROUP BY sharding_key[, ...] WITH ROLLUP
SELECT ... GROUP BY sharding_key[, ...] WITH CUBE
SELECT ... GROUP BY sharding_key[, ...] SETTINGS extremes=1

Possible values:

0 — Disabled.
1 — Enabled.

optimize_dry_run_check_part

When enabled, OPTIMIZE ... DRY RUN validates the resulting merged part using checkDataPart. If the check fails, an exception is thrown.

optimize_empty_string_comparisons

Convert expressions like col = ” or ” = col into empty(col), and col != ” or ” != col into notEmpty(col), only when col is of String or FixedString type.

optimize_extract_common_expressions

Allow extracting common expressions from disjunctions in WHERE, PREWHERE, ON, HAVING and QUALIFY expressions. A logical expression like (A AND B) OR (A AND C) can be rewritten to A AND (B OR C), which might help to utilize:

indices in simple filtering expressions
cross to inner join optimizatio

optimize_functions_to_subcolumns

Enables or disables optimization by transforming some functions to reading subcolumns. This reduces the amount of data to read. These functions can be transformed:

length to read the size0 subcolumn.
empty to read the size0 subcolumn.
notEmpty to read the size0 subcolumn.
isNull to read the null subcolumn.
isNotNull to read the null subcolumn.
count to read the null subcolumn.
mapKeys to read the keys subcolumn.
mapValues to read the values subcolumn.

Possible values:

0 — Optimization disabled.
1 — Optimization enabled.

optimize_inverse_dictionary_lookup

Avoid repeated inverse dictionary lookup by doing faster lookups into a precomputed set of possible key values.

optimize_multiif_to_if

Replace ‘multiIf’ with only one condition to ‘if’.

optimize_normalize_count_variants

Rewrite aggregate functions that semantically equals to count() as count().

optimize_on_insert

Enables or disables data transformation before the insertion, as if merge was done on this block (according to table engine). Possible values:

0 — Disabled.
1 — Enabled.

Example The difference between enabled and disabled: Query:

SET optimize_on_insert = 1;

CREATE TABLE test1 (`FirstTable` UInt32) ENGINE = ReplacingMergeTree ORDER BY FirstTable;

INSERT INTO test1 SELECT number % 2 FROM numbers(5);

SELECT * FROM test1;

SET optimize_on_insert = 0;

CREATE TABLE test2 (`SecondTable` UInt32) ENGINE = ReplacingMergeTree ORDER BY SecondTable;

INSERT INTO test2 SELECT number % 2 FROM numbers(5);

SELECT * FROM test2;

Result:

┌─FirstTable─┐
│          0 │
│          1 │
└────────────┘

┌─SecondTable─┐
│           0 │
│           0 │
│           0 │
│           1 │
│           1 │
└─────────────┘

Note that this setting influences Materialized view behaviour.

optimize_prewhere_after_pushdown

Run a second PREWHERE promotion pass after later query plan optimizations may have deposited additional filters above a MergeTree read step (e.g. predicate pushdown through JOIN, projection rewrites). When an existing PREWHERE is already present, the new filter is AND-merged into it instead of staying as a separate filter step.

optimize_qbit_distance_function_reads

Replace distance functions on QBit data type with equivalent ones that only read the columns necessary for the calculation from the storage.

optimize_read_in_order

Enables ORDER BY optimization in SELECT queries for reading data from MergeTree tables. Possible values:

0 — ORDER BY optimization is disabled.
1 — ORDER BY optimization is enabled.

See Also

ORDER BY Clause

optimize_respect_aliases

If it is set to true, it will respect aliases in WHERE/GROUP BY/ORDER BY, that will help with partition pruning/secondary indexes/optimize_aggregation_in_order/optimize_read_in_order/optimize_trivial_count

optimize_sorting_by_input_stream_properties

Optimize sorting by sorting properties of input stream

optimize_substitute_columns

Use constraints for column substitution. The default is false. Possible values:

true, false

optimize_syntax_fuse_functions

Enables to fuse aggregate functions with identical argument. It rewrites query contains at least two aggregate functions from sum, count or avg with identical argument to sumCount. Possible values:

0 — Functions with identical argument are not fused.
1 — Functions with identical argument are fused.

Example Query:

CREATE TABLE fuse_tbl(a Int8, b Int8) Engine = Log;
SET optimize_syntax_fuse_functions = 1;
EXPLAIN SYNTAX run_query_tree_passes = 1 SELECT sum(a), sum(b), count(b), avg(b) from fuse_tbl FORMAT TSV;

Result:

SELECT
    sum(__table1.a) AS `sum(a)`,
    tupleElement(sumCount(__table1.b), 1) AS `sum(b)`,
    tupleElement(sumCount(__table1.b), 2) AS `count(b)`,
    divide(tupleElement(sumCount(__table1.b), 1), toFloat64(tupleElement(sumCount(__table1.b), 2))) AS `avg(b)`
FROM default.fuse_tbl AS __table1

optimize_throw_if_noop

Enables or disables throwing an exception if an OPTIMIZE query didn’t perform a merge. By default, OPTIMIZE returns successfully even if it didn’t do anything. This setting lets you differentiate these situations and get the reason in an exception message. Possible values:

1 — Throwing an exception is enabled.
0 — Throwing an exception is disabled.

optimize_time_filter_with_preimage

Optimize Date and DateTime predicates by converting functions into equivalent comparisons without conversions (e.g. toYear(col) = 2023 -> col >= '2023-01-01' AND col <= '2023-12-31')

optimize_truncate_order_by_after_group_by_keys

Remove trailing ORDER BY elements once all GROUP BY keys are covered in the ORDER BY prefix.

optimize_uniq_to_count

Rewrite uniq and its variants(except uniqUpTo) to count if subquery has distinct or group by clause.

optimize_using_constraints

Use constraints for query optimization. The default is false. Possible values:

true, false

SQL Reference

Data Types

Engines

Functions

Formats

Settings

System Tables

Data Lakes

optimize_aggregators_of_group_by_keys

optimize_append_index

optimize_arithmetic_operations_in_aggregate_functions

optimize_const_name_size

optimize_count_from_files

optimize_dictget_tuple_element

optimize_distinct_in_order

optimize_distributed_group_by_sharding_key

optimize_dry_run_check_part

optimize_empty_string_comparisons

optimize_extract_common_expressions

optimize_functions_to_subcolumns

optimize_inverse_dictionary_lookup

optimize_multiif_to_if

optimize_normalize_count_variants

optimize_on_insert

optimize_prewhere_after_pushdown

optimize_qbit_distance_function_reads

optimize_read_in_order

optimize_respect_aliases

optimize_sorting_by_input_stream_properties

optimize_substitute_columns

optimize_syntax_fuse_functions

optimize_throw_if_noop

optimize_time_filter_with_preimage

optimize_truncate_order_by_after_group_by_keys

optimize_uniq_to_count

optimize_using_constraints

​optimize_aggregators_of_group_by_keys

​optimize_append_index

​optimize_arithmetic_operations_in_aggregate_functions

​optimize_const_name_size

​optimize_count_from_files

​optimize_dictget_tuple_element

​optimize_distinct_in_order

​optimize_distributed_group_by_sharding_key

​optimize_dry_run_check_part

​optimize_empty_string_comparisons

​optimize_extract_common_expressions

​optimize_functions_to_subcolumns

​optimize_inverse_dictionary_lookup

​optimize_multiif_to_if

​optimize_normalize_count_variants

​optimize_on_insert

​optimize_prewhere_after_pushdown

​optimize_qbit_distance_function_reads

​optimize_read_in_order

​optimize_respect_aliases

​optimize_sorting_by_input_stream_properties

​optimize_substitute_columns

​optimize_syntax_fuse_functions

​optimize_throw_if_noop

​optimize_time_filter_with_preimage

​optimize_truncate_order_by_after_group_by_keys

​optimize_uniq_to_count

​optimize_using_constraints

optimize_aggregators_of_group_by_keys

optimize_append_index

optimize_arithmetic_operations_in_aggregate_functions

optimize_const_name_size

optimize_count_from_files

optimize_dictget_tuple_element

optimize_distinct_in_order

optimize_distributed_group_by_sharding_key

optimize_dry_run_check_part

optimize_empty_string_comparisons

optimize_extract_common_expressions

optimize_functions_to_subcolumns

optimize_inverse_dictionary_lookup

optimize_multiif_to_if

optimize_normalize_count_variants

optimize_on_insert

optimize_prewhere_after_pushdown

optimize_qbit_distance_function_reads

optimize_read_in_order

optimize_respect_aliases

optimize_sorting_by_input_stream_properties

optimize_substitute_columns

optimize_syntax_fuse_functions

optimize_throw_if_noop

optimize_time_filter_with_preimage

optimize_truncate_order_by_after_group_by_keys

optimize_uniq_to_count

optimize_using_constraints