> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-trino-dialect.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 key_expr FROM table GROUP BY key_expr LIMIT n` (with no aggregate functions, window functions or `arrayJoin` in the projection, no `HAVING`/`ORDER BY`/`QUALIFY`/`LIMIT BY`/`DISTINCT`/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

<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.