optimize_trivial_* session settings

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_functions_to_subcolumns

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_count_query

optimize_trivial_group_by_limit_query

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.

optimize_trivial_insert_select

Optimize trivial ‘INSERT INTO table SELECT … FROM TABLES’ query

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.