> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.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="https://clickhouse.com/docs/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 ... FROM table GROUP BY key_expr LIMIT n` (with no `HAVING`/`ORDER BY`/`QUALIFY`/`LIMIT BY`/`DISTINCT`/window clauses and no `GROUP BY` modifiers) by capping the aggregation at `n + offset` distinct keys.

With no aggregate functions in the projection, the cap is applied by setting `max_rows_to_group_by = n + offset` with `group_by_overflow_mode = 'any'` (this form also applies on the shards of distributed queries). With aggregate functions in the projection, the cap is applied only when the server performs the complete aggregation locally: once any aggregation thread exceeds the cap, all threads are restricted to a single shared set of `n + offset` kept keys, so the aggregate values of the returned keys stay exact.

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), when the user has already set a tighter `max_rows_to_group_by`, and with [exact\_rows\_before\_limit](/reference/settings/session-settings/other#exact_rows_before_limit) (the cap would make `rows_before_limit_at_least` report at most `n + offset`).

The aggregate-function form excludes the `GROUP BY` top-K heap of [enable\_group\_by\_top\_k\_optimization](/reference/settings/session-settings/enable#enable_group_by_top_k_optimization) on the same query. The heap is faster, so the cutoff is applied only when the heap does not apply: when it is disabled, when `n + offset` exceeds [query\_plan\_max\_limit\_for\_top\_k\_optimization](/reference/settings/session-settings/query-plan#query_plan_max_limit_for_top_k_optimization), when `max_rows_to_group_by` is set, or when the query plan is serialized.

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

Pair this with an explicit `max_insert_threads` setting: the optimization caps the `SELECT` to
`max_insert_threads` reading threads, which changes how many blocks the `SELECT` produces.

<h2 id="optimize_trivial_view_pushdown_to_distributed">
  optimize\_trivial\_view\_pushdown\_to\_distributed
</h2>

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

<VersionHistory rows={[{"id": "row-1","items": [{"label": "26.8"},{"label": "1"},{"label": "New setting to push the full outer query to shards for trivial views over Distributed tables."}]}]} />

When enabled, for views over [Distributed](/reference/engines/table-engines/special/distributed) tables whose `SELECT` list contains only column references, `*`,
or expressions (but no window functions or scalar subqueries), and that have no aggregation, grouping, ordering, or joins, the full outer query is pushed to
each shard. This allows the shard to apply the view's filters and expressions locally, reducing the amount of data transferred over the network.

Possible values:

* 0 — The optimization is disabled; views over `Distributed` tables are always executed on the coordinator.
* 1 — The optimization is enabled.