enable_* session settings - ClickHouse Documentation

These settings are available in system.settings and are autogenerated from source.

enable_adaptive_memory_spill_scheduler

Trigger processor to spill data into external storage adpatively. grace join is supported at present.

enable_add_distinct_to_in_subqueries

Enable DISTINCT in IN subqueries. This is a trade-off setting: enabling it can greatly reduce the size of temporary tables transferred for distributed IN subqueries and significantly speed up data transfer between shards, by ensuring only unique values are sent. However, enabling this setting adds extra merging effort on each node, as deduplication (DISTINCT) must be performed. Use this setting when network transfer is a bottleneck and the additional merging cost is acceptable.

enable_automatic_decision_for_merging_across_partitions_for_final

If set, ClickHouse will automatically enable this optimization when the partition key expression is deterministic and all columns used in the partition key expression are included in the primary key. This automatic derivation ensures that rows with the same primary key values will always belong to the same partition, making it safe to avoid cross-partition merges.

enable_early_constant_folding

Enable query optimization where we analyze function and subqueries results and rewrite query if there are constants there

enable_extended_results_for_datetime_functions

Enables or disables returning results of type Date32 with extended range (compared to type Date) or DateTime64 with extended range (compared to type DateTime). Possible values:

0 — Functions return Date or DateTime for all types of arguments.
1 — Functions return Date32 or DateTime64 for Date32 or DateTime64 arguments and Date or DateTime otherwise.

The table below shows the behavior of this setting for various date-time functions.

Function	`enable_extended_results_for_datetime_functions = 0`	`enable_extended_results_for_datetime_functions = 1`
`toStartOfYear`	Returns `Date` or `DateTime`	Returns `Date`/`DateTime` for `Date`/`DateTime` input Returns `Date32`/`DateTime64` for `Date32`/`DateTime64` input
`toStartOfISOYear`	Returns `Date` or `DateTime`	Returns `Date`/`DateTime` for `Date`/`DateTime` input Returns `Date32`/`DateTime64` for `Date32`/`DateTime64` input
`toStartOfQuarter`	Returns `Date` or `DateTime`	Returns `Date`/`DateTime` for `Date`/`DateTime` input Returns `Date32`/`DateTime64` for `Date32`/`DateTime64` input
`toStartOfMonth`	Returns `Date` or `DateTime`	Returns `Date`/`DateTime` for `Date`/`DateTime` input Returns `Date32`/`DateTime64` for `Date32`/`DateTime64` input
`toStartOfWeek`	Returns `Date` or `DateTime`	Returns `Date`/`DateTime` for `Date`/`DateTime` input Returns `Date32`/`DateTime64` for `Date32`/`DateTime64` input
`toLastDayOfWeek`	Returns `Date` or `DateTime`	Returns `Date`/`DateTime` for `Date`/`DateTime` input Returns `Date32`/`DateTime64` for `Date32`/`DateTime64` input
`toLastDayOfMonth`	Returns `Date` or `DateTime`	Returns `Date`/`DateTime` for `Date`/`DateTime` input Returns `Date32`/`DateTime64` for `Date32`/`DateTime64` input
`toMonday`	Returns `Date` or `DateTime`	Returns `Date`/`DateTime` for `Date`/`DateTime` input Returns `Date32`/`DateTime64` for `Date32`/`DateTime64` input
`toStartOfDay`	Returns `DateTime` Note: Wrong results for values outside 1970-2149 range	Returns `DateTime` for `Date`/`DateTime` input Returns `DateTime64` for `Date32`/`DateTime64` input
`toStartOfHour`	Returns `DateTime` Note: Wrong results for values outside 1970-2149 range	Returns `DateTime` for `Date`/`DateTime` input Returns `DateTime64` for `Date32`/`DateTime64` input
`toStartOfFifteenMinutes`	Returns `DateTime` Note: Wrong results for values outside 1970-2149 range	Returns `DateTime` for `Date`/`DateTime` input Returns `DateTime64` for `Date32`/`DateTime64` input
`toStartOfTenMinutes`	Returns `DateTime` Note: Wrong results for values outside 1970-2149 range	Returns `DateTime` for `Date`/`DateTime` input Returns `DateTime64` for `Date32`/`DateTime64` input
`toStartOfFiveMinutes`	Returns `DateTime` Note: Wrong results for values outside 1970-2149 range	Returns `DateTime` for `Date`/`DateTime` input Returns `DateTime64` for `Date32`/`DateTime64` input
`toStartOfMinute`	Returns `DateTime` Note: Wrong results for values outside 1970-2149 range	Returns `DateTime` for `Date`/`DateTime` input Returns `DateTime64` for `Date32`/`DateTime64` input
`timeSlot`	Returns `DateTime` Note: Wrong results for values outside 1970-2149 range	Returns `DateTime` for `Date`/`DateTime` input Returns `DateTime64` for `Date32`/`DateTime64` input

enable_full_text_index

Aliases: allow_experimental_full_text_index If set to true, allow using the text index.

enable_global_with_statement

Propagate WITH statements to UNION queries and all subqueries

enable_hdfs_pread

Enable or disables pread for HDFS files. By default, hdfsPread is used. If disabled, hdfsRead and hdfsSeek will be used to read hdfs files.

enable_http_compression

Enables or disables data compression in the response to an HTTP request. For more information, read the HTTP interface description. Possible values:

0 — Disabled.
1 — Enabled.

enable_identifier_resolve_cache

Enable the identifier resolution cache in the query analyzer. The cache shares resolved alias nodes to prevent AST explosion when the same alias is referenced multiple times. Set to false to disable caching if incorrect results are suspected.

enable_job_stack_trace

Output stack trace of a job creator when job results in exception. Disabled by default to avoid performance overhead.

enable_lazy_columns_replication

Enables lazy columns replication in JOIN and ARRAY JOIN, it allows to avoid unnecessary copy of the same rows multiple times in memory.

enable_materialized_cte

Enable materialized common table expressions, it will be preferred over enable_global_with_statement

enable_memory_bound_merging_of_aggregation_results

Enable memory bound merging strategy for aggregation.

enable_multiple_prewhere_read_steps

Move more conditions from WHERE to PREWHERE and do reads from disk and filtering in multiple steps if there are multiple conditions combined with AND

enable_named_columns_in_function_tuple

Generate named tuples in function tuple() when all names are unique and can be treated as unquoted identifiers.

enable_order_by_all

Enables or disables sorting with ORDER BY ALL syntax, see ORDER BY. Possible values:

0 — Disable ORDER BY ALL.
1 — Enable ORDER BY ALL.

Example Query:

CREATE TABLE TAB(C1 Int, C2 Int, ALL Int) ENGINE=Memory();

INSERT INTO TAB VALUES (10, 20, 30), (20, 20, 10), (30, 10, 20);

SELECT * FROM TAB ORDER BY ALL; -- returns an error that ALL is ambiguous

SELECT * FROM TAB ORDER BY ALL SETTINGS enable_order_by_all = 0;

Result:

┌─C1─┬─C2─┬─ALL─┐
│ 20 │ 20 │  10 │
│ 30 │ 10 │  20 │
│ 10 │ 20 │  30 │
└────┴────┴─────┘

enable_parallel_blocks_marshalling

Affects only distributed queries. If enabled, blocks will be (de)serialized and (de)compressed on pipeline threads (i.e. with higher parallelism that what we have by default) before/after sending to the initiator.

enable_parsing_to_custom_serialization

If true then data can be parsed directly to columns with custom serialization (e.g. Sparse) according to hints for serialization got from the table.

enable_producing_buckets_out_of_order_in_aggregation

Allow memory-efficient aggregation (see distributed_aggregation_memory_efficient) to produce buckets out of order. It may improve performance when aggregation bucket sizes are skewed by letting a replica to send buckets with higher id-s to the initiator while it is still processing some heavy buckets with lower id-s. The downside is potentially higher memory usage.

enable_reads_from_query_cache

If turned on, results of SELECT queries are retrieved from the query cache. Possible values:

0 - Disabled
1 - Enabled

enable_s3_requests_logging

Enable very explicit logging of S3 requests. Makes sense for debug only.

enable_scalar_subquery_optimization

If it is set to true, prevent scalar subqueries from (de)serializing large scalar values and possibly avoid running the same subquery more than once.

enable_scopes_for_with_statement

If disabled, declarations in parent WITH cluases will behave the same scope as they declared in the current scope. Note that this is a compatibility setting for the analyzer to allow running some invalid queries that old analyzer could execute.

enable_sharding_aggregator

Enables sharded GROUP BY optimization that distributes rows across threads by hashing the grouping key, so each thread aggregates a disjoint subset of keys without a merge phase. This is efficient for high-cardinality keys with evenly distributed data, but may suffer from highly skewed key distributions or queries with very few distinct keys. Possible values:

0 — Sharded aggregation optimization is disabled.
1 — Sharded aggregation optimization is enabled.

enable_shared_storage_snapshot_in_query

If enabled, all subqueries within a single query will share the same StorageSnapshot for each table. This ensures a consistent view of the data across the entire query, even if the same table is accessed multiple times. This is required for queries where internal consistency of data parts is important. Example:

SELECT
    count()
FROM events
WHERE (_part, _part_offset) IN (
    SELECT _part, _part_offset
    FROM events
    WHERE user_id = 42
)

Without this setting, the outer and inner queries may operate on different data snapshots, leading to incorrect results.

Enabling this setting disables the optimization which removes unnecessary data parts from snapshots once the planning stage is complete. As a result, long-running queries may hold onto obsolete parts for their entire duration, delaying part cleanup and increasing storage pressure.This setting currently applies only to tables from the MergeTree family.

Possible values:

0 - Disabled
1 - Enabled

enable_sharing_sets_for_mutations

Allow sharing set objects build for IN subqueries between different tasks of the same mutation. This reduces memory usage and CPU consumptio

enable_streaming_queries

Allow SELECT ... FROM t STREAM [CURSOR '{...}'] continuous queries. When off, any table expression using the STREAM modifier is rejected at plan-build time. This is the umbrella gate for the streaming-queries feature; additional capabilities may be gated by their own settings.

enable_time_time64_type

Aliases: allow_experimental_time_time64_type Allows creation of Time and Time64 data types.

enable_unaligned_array_join

Allow ARRAY JOIN with multiple arrays that have different sizes. When this settings is enabled, arrays will be resized to the longest one.

enable_url_encoding

Allows to enable/disable decoding/encoding path in uri in URL engine tables. Disabled by default.

enable_vertical_final

If enable, remove duplicated rows during FINAL by marking rows as deleted and filtering them later instead of merging rows

enable_writes_to_query_cache

If turned on, results of SELECT queries are stored in the query cache. Possible values:

0 - Disabled
1 - Enabled

​enable_adaptive_memory_spill_scheduler

​enable_add_distinct_to_in_subqueries

​enable_automatic_decision_for_merging_across_partitions_for_final

​enable_early_constant_folding

​enable_extended_results_for_datetime_functions

​enable_full_text_index

​enable_global_with_statement

​enable_hdfs_pread

​enable_http_compression

​enable_identifier_resolve_cache

​enable_job_stack_trace

​enable_lazy_columns_replication

​enable_materialized_cte

​enable_memory_bound_merging_of_aggregation_results

​enable_multiple_prewhere_read_steps

​enable_named_columns_in_function_tuple

​enable_order_by_all

​enable_parallel_blocks_marshalling

​enable_parsing_to_custom_serialization

​enable_producing_buckets_out_of_order_in_aggregation

​enable_reads_from_query_cache

​enable_s3_requests_logging

​enable_scalar_subquery_optimization

​enable_scopes_for_with_statement

​enable_sharding_aggregator

​enable_shared_storage_snapshot_in_query

​enable_sharing_sets_for_mutations

​enable_streaming_queries

​enable_time_time64_type

​enable_unaligned_array_join

​enable_url_encoding

​enable_vertical_final

​enable_writes_to_query_cache

enable_adaptive_memory_spill_scheduler

enable_add_distinct_to_in_subqueries

enable_automatic_decision_for_merging_across_partitions_for_final

enable_early_constant_folding

enable_extended_results_for_datetime_functions

enable_full_text_index

enable_global_with_statement

enable_hdfs_pread

enable_http_compression

enable_identifier_resolve_cache

enable_job_stack_trace

enable_lazy_columns_replication

enable_materialized_cte

enable_memory_bound_merging_of_aggregation_results

enable_multiple_prewhere_read_steps

enable_named_columns_in_function_tuple

enable_order_by_all

enable_parallel_blocks_marshalling

enable_parsing_to_custom_serialization

enable_producing_buckets_out_of_order_in_aggregation

enable_reads_from_query_cache

enable_s3_requests_logging

enable_scalar_subquery_optimization

enable_scopes_for_with_statement

enable_sharding_aggregator

enable_shared_storage_snapshot_in_query

enable_sharing_sets_for_mutations

enable_streaming_queries

enable_time_time64_type

enable_unaligned_array_join

enable_url_encoding

enable_vertical_final

enable_writes_to_query_cache