enable_join_fixed_hash_table_conversion
Enable converting the hash table to a flat array for joins when the key is a single integer with a small value range.enable_join_key_only_hash_tables
Use hash tables that store the join keys alone, without a reference to a right row, for joins whose result can never contain a value taken from a right row:LEFT ANTI, and LEFT SEMI when no right column is selected. Such a table has a smaller cell and lets the right blocks be dropped instead of stored.
enable_join_runtime_filters
Filter left side by set of JOIN keys collected from the right side at runtime.enable_join_runtime_filters_index_analysis
Prune granules on the probe side of a JOIN with the runtime filter collected from the build side, seeenable_join_runtime_filters.
Only has an effect if use_skip_indexes_on_data_read = 1.
Only a join key that is a primary key column of the probe side, or is covered by a minmax, set or bloom_filter skip index, can be pruned.
If the runtime filter kept the exact key values, the pruning predicate is an IN set of them, otherwise the minimum/maximum key range is used (this has a lower pruning power).
Takes effect only when the probe side of the join is read locally. The descriptors that drive the pruning are attached to the read step while the query plan is optimized, and they are not carried over when that step is rebuilt for remote execution, so the granule pruning does not happen with parallel replicas (enable_parallel_replicas = 1) or with a distributed query plan (make_distributed_plan = 1). In those modes the setting is a no-op: the query returns the same result and the JOIN runtime filter itself behaves exactly as it does with this setting disabled, only the granule pruning is lost.
The granule pruning is also skipped for a probe side read with FINAL (the pruning is not implemented for FINAL reads, and optimizeLazyFinal rebuilds such a read without the descriptors), and for a table with pending data or ALTER mutations or patch parts. These cases are a no-op in the same sense.
enable_join_transitive_predicates
Infer transitive equi-join predicates from existing join conditions. For example, givenA.x = B.x and B.x = C.x, a synthetic A.x = C.x predicate
is added so the join order optimizer can consider direct (A JOIN C) plans.