Turn on enable_join_transitive_predicates by default by davenger · Pull Request #103724 · ClickHouse/ClickHouse · GitHub
Skip to content

Turn on enable_join_transitive_predicates by default#103724

Merged
davenger merged 7 commits into
masterfrom
enable_join_transitive_predicates
Jun 9, 2026
Merged

Turn on enable_join_transitive_predicates by default#103724
davenger merged 7 commits into
masterfrom
enable_join_transitive_predicates

Conversation

@davenger

@davenger davenger commented Apr 29, 2026 •
edited by robot-clickhouse
Loading

Copy link
Copy Markdown
Member

Changelog category (leave one):

  • Improvement

Changelog entry (a user-readable short description of the changes that goes into CHANGELOG.md):

Turn on enable_join_transitive_predicates by default

Documentation entry for user-facing changes

  • Documentation is written (mandatory for new features)

Version info

  • Merged into: 26.6.1.566

@clickhouse-gh

clickhouse-gh Bot commented Apr 29, 2026 •
edited
Loading

Copy link
Copy Markdown
Contributor

@clickhouse-gh clickhouse-gh Bot added the pr-improvement Pull request with some product improvements label Apr 29, 2026
clickhouse-gh[bot]
clickhouse-gh Bot reviewed Apr 30, 2026
View reviewed changes
Comment thread tests/queries/0_stateless/02498_storage_join_key_positions.sql Outdated
clickhouse-gh[bot]
clickhouse-gh Bot reviewed Apr 30, 2026
View reviewed changes
Comment thread tests/queries/0_stateless/02498_storage_join_key_positions.reference Outdated
clickhouse-gh[bot]
clickhouse-gh Bot reviewed May 1, 2026
View reviewed changes
Comment thread tests/queries/0_stateless/02498_storage_join_key_positions.sql
SELECT * FROM t1 ALL INNER JOIN tj ON t1.key1 = tj.key1 AND t1.key2 = tj.key2 AND t1.key3 = tj.key3 AND t1.key1 = tj.key1 SETTINGS enable_join_transitive_predicates = 0; -- { serverError INCOMPATIBLE_TYPE_OF_JOIN }

SELECT '--- duplicated predicate removed ---';
SELECT * FROM t1 ALL INNER JOIN tj ON t1.key1 = tj.key1 AND t1.key2 = tj.key2 AND t1.key3 = tj.key3 AND t1.key1 = tj.key1 SETTINGS enable_analyzer = 1, enable_join_transitive_predicates = 1, query_plan_optimize_join_order_limit = 10;

@clickhouse-gh clickhouse-gh Bot May 1, 2026

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This test still forces enable_join_transitive_predicates = 1, so it does not actually verify the PR's main behavior change (the new default). If someone accidentally flips the default back to false, this test would continue to pass.

Please add a case that relies on the default value (no explicit enable_join_transitive_predicates override) and validates the query succeeds under that default.

@arsenmuk

arsenmuk commented May 5, 2026

Copy link
Copy Markdown
Member

Hi @davenger, should we also update https://clickhouse.com/docs/operations/settings/settings#enable_join_transitive_predicates to remove experimental flag?

@arsenmuk arsenmuk self-assigned this May 11, 2026
@clickhouse-gh

clickhouse-gh Bot commented Jun 8, 2026

Copy link
Copy Markdown
Contributor

📊 Cloud Performance Report

🟢 AI verdict: improvement — 2 query(s) improved out of 38 analysed

This PR turns on the join-transitive-predicates optimizer setting by default. The two TPC-H join queries (Q7 -31.9%, Q20 -45.1%) show large, consistent speedups that match the change directly: the optimizer can now infer extra join predicates and pick better join orders. The three flagged ClickBench improvements (Q15, Q32, Q34) run against a single flat table with no joins, so this PR cannot plausibly affect them; those deltas are run-to-run variance and were downgraded. Net: a real join-planning win on multi-table workloads, no regressions.

clickbench

⚠️ 3 inconclusive

Flagged queries (3 of 43)
Query Verdict Baseline med (ms) PR med (ms) Change q-value Hint
⚠️ 15 not_sure 250 223 -10.8% <0.0001 ClickBench is single-table; the join-predicates default flip can't touch this query. Run-to-run variance.
⚠️ 32 not_sure 1402 1320 -5.8% <0.0001 No joins in ClickBench; PR only changes join optimizer default. Off-path, treat as noise.
⚠️ 34 not_sure 1537 1458 -5.2% <0.0001 Single-table scan unaffected by join-transitive-predicates; -5% is run-to-run variance, not a PR effect.

q-value = BH-FDR adjusted p; smaller is stronger evidence. MIRAI flags a query when q < fdr_q (default 0.10) — the value the verdict is based on.

tpch_adapted_1_official

🟢 2 improved

Flagged queries (2 of 22)
Query Verdict Baseline med (ms) PR med (ms) Change q-value Hint
🟢 7 improvement 113 77 -31.9% <0.0001 plan_change: Multi-table join: enabling transitive predicates lets the optimizer pick a better join order. -31.9% is a clear shift.
🟢 20 improvement 246 135 -45.1% <0.0001 plan_change: Join-heavy query; transitive predicates open better join orders. Large delta despite noisier baseline.

q-value = BH-FDR adjusted p; smaller is stronger evidence. MIRAI flags a query when q < fdr_q (default 0.10) — the value the verdict is based on.

Debug info
  • StressHouse run: 218e25c9-5801-4b92-821c-14625387b850
  • MIRAI run: b30157a1-dd6f-4fcb-901c-d816b428a463
  • PR check IDs:
    • clickbench_40077_1780944322
    • clickbench_40090_1780944322
    • clickbench_40096_1780944323
    • tpch_adapted_1_official_40102_1780944323
    • tpch_adapted_1_official_40112_1780944323
    • tpch_adapted_1_official_40119_1780944323

@clickhouse-gh

clickhouse-gh Bot commented Jun 8, 2026

Copy link
Copy Markdown
Contributor

LLVM Coverage Report

Metric Baseline Current Δ
Lines 84.60% 84.50% -0.10%
Functions 92.30% 92.30% +0.00%
Branches 77.20% 77.20% +0.00%

Changed lines: Changed C/C++ lines covered by tests: 17/17 (100.00%) | Lost baseline coverage: none · Uncovered code

Full report · Diff report

@davenger

davenger commented Jun 9, 2026

Copy link
Copy Markdown
Member Author

@davenger davenger added this pull request to the merge queue Jun 9, 2026
Merged via the queue into master with commit 5523eab Jun 9, 2026
167 checks passed
@davenger davenger deleted the enable_join_transitive_predicates branch June 9, 2026 15:40
@robot-ch-test-poll1 robot-ch-test-poll1 added the pr-synced-to-cloud The PR is synced to the cloud repo label Jun 9, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@clickhouse-gh clickhouse-gh[bot] clickhouse-gh[bot] left review comments
@arsenmuk arsenmuk arsenmuk approved these changes

Assignees

@arsenmuk arsenmuk

Labels

pr-improvement Pull request with some product improvements pr-synced-to-cloud The PR is synced to the cloud repo

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@davenger @arsenmuk @robot-ch-test-poll1