> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# optimize_* session settings

> ClickHouse session settings in the optimize_* generated group.

export const SettingsInfoBlock = ({type, default_value, changeable_without_restart}) => {
  return <div className="not-prose" style={{
    display: "flex",
    flexWrap: "wrap",
    alignItems: "baseline",
    columnGap: "0.5rem",
    rowGap: "0.125rem",
    margin: "0.375rem 0",
    fontSize: "0.8125rem",
    lineHeight: "1.125rem"
  }}>
      <div style={{
    fontWeight: 600,
    opacity: 0.72
  }}>Type</div>
      <div style={{
    overflowWrap: "anywhere"
  }}>{type}</div>
      <div style={{
    fontWeight: 600,
    opacity: 0.72,
    marginInlineStart: "0.5rem"
  }}>Default</div>
      <div style={{
    overflowWrap: "anywhere"
  }}>{default_value}</div>
      {changeable_without_restart && <div style={{
    fontWeight: 600,
    opacity: 0.72,
    marginInlineStart: "0.5rem"
  }}>
          Changeable without restart
        </div>}
      {changeable_without_restart && <div style={{
    overflowWrap: "anywhere"
  }}>
          {changeable_without_restart}
        </div>}
    </div>;
};

These settings are available in [system.settings](/docs/reference/system-tables/settings) and are autogenerated from [source](https://github.com/ClickHouse/ClickHouse/blob/master/src/Core/Settings.cpp).

<h2 id="optimize_aggregators_of_group_by_keys">
  optimize\_aggregators\_of\_group\_by\_keys
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

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

<h2 id="optimize_append_index">
  optimize\_append\_index
</h2>

<SettingsInfoBlock type="Bool" default_value="0" />

Use [constraints](/docs/reference/statements/create/table#constraints) in order to append index condition. The default is `false`.

Possible values:

* true, false

<h2 id="optimize_arithmetic_operations_in_aggregate_functions">
  optimize\_arithmetic\_operations\_in\_aggregate\_functions
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

Move arithmetic operations out of aggregation functions

<h2 id="optimize_const_name_size">
  optimize\_const\_name\_size
</h2>

<SettingsInfoBlock type="Int64" default_value="256" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "25.11"},{"label": "256"},{"label": "Replace with scalar and use hash as a name for large constants (size is estimated by name length)"}]}]} />

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.

<h2 id="optimize_count_from_files">
  optimize\_count\_from\_files
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

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.

<h2 id="optimize_dictget_tuple_element">
  optimize\_dictget\_tuple\_element
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.5"},{"label": "1"},{"label": "Rewrite tupleElement(dictGet(..., tuple_of_attrs, ...), N) into a single-attribute dictGet call."}]}]} />

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.

<h2 id="optimize_distinct_in_order">
  optimize\_distinct\_in\_order
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

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

<h2 id="optimize_distributed_group_by_sharding_key">
  optimize\_distributed\_group\_by\_sharding\_key
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

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.

See also:

* [distributed\_group\_by\_no\_merge](/docs/reference/settings/session-settings/distributed#distributed_group_by_no_merge)
* [distributed\_push\_down\_limit](/docs/reference/settings/session-settings/distributed#distributed_push_down_limit)
* [optimize\_skip\_unused\_shards](/docs/reference/settings/session-settings/optimize-skip#optimize_skip_unused_shards)

<Note>
  Right now it requires `optimize_skip_unused_shards` (the reason behind this is that one day it may be enabled by default, and it will work correctly only if data was inserted via Distributed table, i.e. data is distributed according to sharding\_key).
</Note>

<h2 id="optimize_dry_run_check_part">
  optimize\_dry\_run\_check\_part
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.2"},{"label": "1"},{"label": "New setting"}]}]} />

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

<h2 id="optimize_empty_string_comparisons">
  optimize\_empty\_string\_comparisons
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "25.10"},{"label": "1"},{"label": "A new setting."}]}]} />

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.

<h2 id="optimize_extract_common_expressions">
  optimize\_extract\_common\_expressions
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "25.1"},{"label": "1"},{"label": "Optimize WHERE, PREWHERE, ON, HAVING and QUALIFY expressions by extracting common expressions out from disjunction of conjunctions."}]}, {"id": "row-2","items": [{"label": "24.12"},{"label": "0"},{"label": "Introduce setting to optimize WHERE, PREWHERE, ON, HAVING and QUALIFY expressions by extracting common expressions out from disjunction of conjunctions."}]}]} />

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

<h2 id="optimize_functions_to_subcolumns">
  optimize\_functions\_to\_subcolumns
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "24.8"},{"label": "1"},{"label": "Enabled settings by default"}]}]} />

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](/docs/reference/functions/regular-functions/array-functions#length) to read the [size0](/docs/reference/data-types/array#array-size) subcolumn.
* [empty](/docs/reference/functions/regular-functions/array-functions#empty) to read the [size0](/docs/reference/data-types/array#array-size) subcolumn.
* [notEmpty](/docs/reference/functions/regular-functions/array-functions#notEmpty) to read the [size0](/docs/reference/data-types/array#array-size) subcolumn.
* [isNull](/docs/reference/functions/regular-functions/functions-for-nulls#isNull) to read the [null](/docs/reference/data-types/nullable#finding-null) subcolumn.
* [isNotNull](/docs/reference/functions/regular-functions/functions-for-nulls#isNotNull) to read the [null](/docs/reference/data-types/nullable#finding-null) subcolumn.
* [count](/docs/reference/functions/aggregate-functions/count) to read the [null](/docs/reference/data-types/nullable#finding-null) subcolumn.
* [mapKeys](/docs/reference/functions/regular-functions/tuple-map-functions#mapKeys) to read the [keys](/docs/reference/data-types/map#reading-subcolumns-of-map) subcolumn.
* [mapValues](/docs/reference/functions/regular-functions/tuple-map-functions#mapValues) to read the [values](/docs/reference/data-types/map#reading-subcolumns-of-map) subcolumn.

Possible values:

* 0 — Optimization disabled.
* 1 — Optimization enabled.

<h2 id="optimize_inverse_dictionary_lookup">
  optimize\_inverse\_dictionary\_lookup
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "25.12"},{"label": "1"},{"label": "New setting"}]}]} />

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

<h2 id="optimize_multiif_to_if">
  optimize\_multiif\_to\_if
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

Replace 'multiIf' with only one condition to 'if'.

<h2 id="optimize_normalize_count_variants">
  optimize\_normalize\_count\_variants
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "21.3"},{"label": "1"},{"label": "Rewrite aggregate functions that semantically equals to count() as count() by default"}]}]} />

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

<h2 id="optimize_on_insert">
  optimize\_on\_insert
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "21.1"},{"label": "1"},{"label": "Enable data optimization on INSERT by default for better user experience"}]}]} />

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:

```sql theme={null}
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:

```text theme={null}
┌─FirstTable─┐
│          0 │
│          1 │
└────────────┘

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

Note that this setting influences [Materialized view](/docs/reference/statements/create/view#materialized-view) behaviour.

<h2 id="optimize_prewhere_after_pushdown">
  optimize\_prewhere\_after\_pushdown
</h2>

<SettingsInfoBlock type="Bool" default_value="0" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.6"},{"label": "0"},{"label": "New setting that enables a second PREWHERE promotion pass to merge filters deposited above a MergeTree read step by later optimizations (predicate pushdown through JOIN, projection rewrites) into the existing PREWHERE chain."}]}]} />

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.

<h2 id="optimize_qbit_distance_function_reads">
  optimize\_qbit\_distance\_function\_reads
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "25.10"},{"label": "1"},{"label": "New setting"}]}]} />

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

<h2 id="optimize_read_in_order">
  optimize\_read\_in\_order
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

Enables [ORDER BY](/docs/reference/statements/select/order-by#optimization-of-data-reading) optimization in [SELECT](/docs/reference/statements/select/index) queries for reading data from [MergeTree](/docs/reference/engines/table-engines/mergetree-family/mergetree) tables.

Possible values:

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

**See Also**

* [ORDER BY Clause](/docs/reference/statements/select/order-by#optimization-of-data-reading)

<h2 id="optimize_respect_aliases">
  optimize\_respect\_aliases
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

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

<h2 id="optimize_sorting_by_input_stream_properties">
  optimize\_sorting\_by\_input\_stream\_properties
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

Optimize sorting by sorting properties of input stream

<h2 id="optimize_substitute_columns">
  optimize\_substitute\_columns
</h2>

<SettingsInfoBlock type="Bool" default_value="0" />

Use [constraints](/docs/reference/statements/create/table#constraints) for column substitution. The default is `false`.

Possible values:

* true, false

<h2 id="optimize_syntax_fuse_functions">
  optimize\_syntax\_fuse\_functions
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.3"},{"label": "1"},{"label": "The optimization is production-ready"}]}]} />

Enables to fuse aggregate functions with identical argument. It rewrites query contains at least two aggregate functions from [sum](/docs/reference/functions/aggregate-functions/sum), [count](/docs/reference/functions/aggregate-functions/count) or [avg](/docs/reference/functions/aggregate-functions/avg) with identical argument to [sumCount](/docs/reference/functions/aggregate-functions/sumCount).

Possible values:

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

**Example**

Query:

```sql theme={null}
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:

```text theme={null}
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
```

<h2 id="optimize_throw_if_noop">
  optimize\_throw\_if\_noop
</h2>

<SettingsInfoBlock type="Bool" default_value="0" />

Enables or disables throwing an exception if an [OPTIMIZE](/docs/reference/statements/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.

<h2 id="optimize_time_filter_with_preimage">
  optimize\_time\_filter\_with\_preimage
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "24.2"},{"label": "1"},{"label": "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 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'`)

<h2 id="optimize_truncate_order_by_after_group_by_keys">
  optimize\_truncate\_order\_by\_after\_group\_by\_keys
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.4"},{"label": "1"},{"label": "Remove trailing ORDER BY elements once all GROUP BY keys are covered in the ORDER BY prefix."}]}]} />

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

<h2 id="optimize_uniq_to_count">
  optimize\_uniq\_to\_count
</h2>

<SettingsInfoBlock type="Bool" default_value="1" />

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

<h2 id="optimize_using_constraints">
  optimize\_using\_constraints
</h2>

<SettingsInfoBlock type="Bool" default_value="0" />

Use [constraints](/docs/reference/statements/create/table#constraints) for query optimization. The default is `false`.

Possible values:

* true, false