Skip to main content
These settings are available in system.settings and are autogenerated from source.

​
optimize_trivial_approximate_count_query

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.

​
optimize_trivial_count_query

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_trivial_count_with_sparsity_filter

Extends the 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_group_by_limit_query

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 (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 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, when max_rows_to_group_by is set, or when the query plan is serialized. Possible values:
  • 0 — Optimization disabled.
  • 1 — Optimization enabled.

​
optimize_trivial_insert_select

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.

​
optimize_trivial_view_pushdown_to_distributed

When enabled, for views over 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.
Last modified on September 28, 2026