> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-vortex-format.mintlify.site/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 BetaBadge = ({link, galaxyTrack, galaxyEvent}) => {
  if (link) {
    return <a href={link} target="_blank" rel="noopener noreferrer" className="betaBadge" onClick={galaxyTrack && galaxyEvent ? galaxyOnClick(galaxyEvent) : undefined}>
                <span>Beta</span>
            </a>;
  }
  return <a href="/reference/settings/beta-and-experimental-features#beta-features" className="betaBadge">
            <span>Beta feature</span>
        </a>;
};

export const VersionHistory = ({rows = []}) => {
  if (rows.length === 0) {
    return null;
  }
  const headers = ["Version", "Default value", "Comment"];
  const border = "1px solid rgba(128, 128, 128, 0.3)";
  const cell = {
    border,
    padding: "0.25rem 0.5rem",
    textAlign: "start",
    verticalAlign: "top"
  };
  return <details className="not-prose" style={{
    border,
    borderRadius: "0.5rem",
    margin: "0.5rem 0",
    padding: "0.5rem 0.75rem",
    fontSize: "0.8125rem",
    lineHeight: "1.125rem"
  }}>
      <summary style={{
    cursor: "pointer",
    fontWeight: 600,
    opacity: 0.72
  }}>
        Version history
      </summary>
      <table style={{
    borderCollapse: "collapse",
    width: "100%",
    margin: "0.5rem 0 0"
  }}>
        <thead>
          <tr>
            {headers.map(header => <th key={header} style={{
    ...cell,
    fontWeight: 600,
    opacity: 0.72
  }}>
                {header}
              </th>)}
          </tr>
        </thead>
        <tbody>
          {rows.map((row, row_index) => <tr key={row.id ?? row_index}>
              {(row.items ?? []).map((item, item_index) => <td key={item_index} style={{
    ...cell,
    overflowWrap: "anywhere"
  }}>
                  {item?.label}
                </td>)}
            </tr>)}
        </tbody>
      </table>
    </details>;
};

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](/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](/reference/settings/session-settings/optimize#optimize_functions_to_subcolumns)

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

<BetaBadge />

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

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.8"},{"label": "1"},{"label": "Promote to BETA and enable by default: serve `SELECT count() FROM t WHERE <pred>` from the persisted per-column `num_defaults` \/ `num_rows` counters when `<pred>` partitions rows into defaults vs non-defaults. Requires the MergeTree setting `compute_exact_num_defaults_for_sparse_columns` (also enabled by default now)."}]}, {"id": "row-2","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](/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](/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