> ## 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_trivial_* session settings

> ClickHouse session settings in the optimize_trivial_* 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>;
};

export const ExperimentalBadge = () => {
  return <div className="experimentalBadge">
            <div className="experimentalIcon">
            <svg width="16" height="16" viewBox="0 0 16 16" fill="none" xmlns="http://www.w3.org/2000/svg">
                <path strokeWidth="1.25" d="M5.5 2H10.5" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.25" d="M9.50015 2V6.19625L13.4283 12.7425C13.4738 12.8183 13.4985 12.9049 13.4996 12.9934C13.5008 13.0818 13.4785 13.169 13.435 13.246C13.3914 13.323 13.3283 13.3871 13.2519 13.4317C13.1755 13.4764 13.0886 13.4999 13.0002 13.5H3.00015C2.91164 13.5 2.8247 13.4766 2.74822 13.432C2.67174 13.3874 2.60847 13.3233 2.56487 13.2463C2.52126 13.1693 2.49889 13.082 2.50004 12.9935C2.50119 12.905 2.52582 12.8184 2.5714 12.7425L6.50015 6.19625V2" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
                <path strokeWidth="1.25" d="M4.47656 9.56754C5.30344 9.41254 6.47656 9.47942 7.99969 10.25C10.0153 11.2707 11.4216 11.0569 12.2184 10.7282" stroke="currentColor" strokeLinecap="round" strokeLinejoin="round" />
            </svg>
        </div>
            Experimental feature. <u><a href="/docs/docs/beta-and-experimental-features#experimental-features">Learn more.</a></u>
        </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_trivial_approximate_count_query">
  optimize\_trivial\_approximate\_count\_query
</h2>

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

Use an approximate value for trivial count optimization of storages that support such estimation, for example, EmbeddedRocksDB.

Possible values:

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

<h2 id="optimize_trivial_count_query">
  optimize\_trivial\_count\_query
</h2>

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

Enables or disables the optimization to trivial query `SELECT count() FROM table` using metadata from MergeTree. If you need to use row-level security, disable this setting.

Possible values:

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

See also:

* [optimize\_functions\_to\_subcolumns](/docs/reference/settings/session-settings/optimize#optimize_functions_to_subcolumns)

<h2 id="optimize_trivial_count_with_sparsity_filter">
  optimize\_trivial\_count\_with\_sparsity\_filter
</h2>

<ExperimentalBadge />

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

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.7"},{"label": "0"},{"label": "New (experimental) setting to serve `SELECT count() FROM t WHERE <pred>` from per-column `num_defaults` \/ `num_rows` recorded in `serialization.json` when `<pred>` partitions rows into defaults vs non-defaults."}]}]} />

Extends the [optimize\_trivial\_count\_query](/docs/reference/settings/session-settings/optimize-trivial#optimize_trivial_count_query) optimization to
queries of the form `SELECT count() FROM t WHERE col <op> const`, where `<op> const`
exactly partitions rows into defaults and non-defaults of `col`. The count is then
served from the per-column `num_defaults` / `num_rows` counters that MergeTree already
keeps in `serialization.json`, with no data scan.

Patterns recognised:

* `col = default(col)` / `col != default(col)` for `Int*` / `UInt*`, `String` /
  `FixedString`, `Date` / `DateTime` / `DateTime64`, `Decimal*`, `UUID`, `IPv4` / `IPv6`.
* `IS NULL` / `IS NOT NULL` on `Nullable` columns.
* `empty(col)` / `notEmpty(col)` on `String` columns.
* `col = true` / `col != true` on `Bool` columns.
* `col > 0`, `col >= 1`, `col < 1`, `col <= 0` on unsigned integer columns.
* Bare `col` / `NOT col` on `Int*`, `UInt*`, `Bool` columns (truthy test).

The equality patterns are not applied to `Float*`, `Enum*`, `Nullable`, `LowCardinality`,
or composite types (`Tuple`, `Array`, `Map`, ...) — for these the count is served from the
regular scan path.

To take effect, the per-part `num_defaults` counter must be exact. Enable the MergeTree
table setting `compute_exact_num_defaults_for_sparse_columns` on the target table before
inserts and merges. Parts written without it are silently opted out of the rewrite, so
enabling `optimize_trivial_count_with_sparsity_filter` alone is not enough.

For the `IS NULL` / `IS NOT NULL` patterns on `Nullable` columns, the column must also
have a `num_defaults` entry in `serialization.json`, which only happens when the MergeTree
table setting `nullable_serialization_version` is set to `allow_sparse` at insert /
merge time. With the default value `basic` `Nullable` columns get no per-column entry, so
the optimization silently does not apply.

Possible values:

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

See also:

* [optimize\_trivial\_count\_query](/docs/reference/settings/session-settings/optimize-trivial#optimize_trivial_count_query)

<h2 id="optimize_trivial_group_by_limit_query">
  optimize\_trivial\_group\_by\_limit\_query
</h2>

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

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.5"},{"label": "1"},{"label": "New setting that limits aggregation to at most LIMIT distinct keys for `SELECT key_expr FROM t GROUP BY key_expr LIMIT n` queries."}]}]} />

Enables or disables the optimization of a trivial query `SELECT key_expr FROM table GROUP BY key_expr LIMIT n` (with no aggregate functions in the projection, no `HAVING`/`ORDER BY`/`LIMIT BY`/window clauses, and no `GROUP BY` modifiers) by setting `max_rows_to_group_by = n + offset` with `group_by_overflow_mode = 'any'`. The aggregation stops once `n + offset` distinct keys are produced.

The optimization is suppressed when the user has explicitly set `group_by_overflow_mode` to a non-`any` value (to preserve their explicit `throw`/`break` contract), and when the user has already set a tighter `max_rows_to_group_by` (the optimization would be a no-op).

Possible values:

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

<h2 id="optimize_trivial_insert_select">
  optimize\_trivial\_insert\_select
</h2>

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

<VersionHistory rows={[{"id": "row-1","items": [{"label": "24.7"},{"label": "0"},{"label": "The optimization does not make sense in many cases."}]}]} />

Optimize trivial 'INSERT INTO table SELECT ... FROM TABLES' query