## Problem ClickHouse inlines CTEs — every CTE reference re-executes the CTE body. The compiler creates CTEs across multiple passes (lower, optimize, deduplicate, security), each adding independent scans of the same table. A 3-node query on Definition can scan `gl_definition` 3-4x and `gl_edge` 3-8x. ## Duplication patterns Two categories: node table re-scans and edge table re-scans. ### Node table re-scans | # | Pattern | Passes | Status | |---|---|---|---| | 1 | `_nf_*` CTE + dedup subquery inlining | lower + deduplicate | Fixed (!1119) — inlines CTE WHERE into dedup subquery | | 6 | `_target_*_ids` + FROM JOIN | optimize + lower | Same class as #1, handled by same fix | | 8 | Dedup subquery re-evaluates `_nf_*` via InSubquery | deduplicate + lower | Same class as #1, handled by same fix | **Limitations of the fix:** The inlining only fires when the `_nf_*` CTE's WHERE is pure user filters (node_ids, property filters, id_range). Falls back to InSubquery in two cases: - **Cascade-derived CTEs**: Created by `narrow_joined_nodes_via_pinned_neighbors`. Their WHERE references edge aliases (`_ce.source_id`) that don't exist in the dedup subquery scope. Can't inline. - **CTEs with cascade injection**: `cascade_node_filter_ctes` appends `id IN (SELECT id FROM _cascade_target)` to a lowerer-created `_nf_*` CTE. After injection the WHERE contains an InSubquery, so inlining falls back even though the original user filters were inlinable. This affects multi-rel traversals where a pinned node cascades through relationships. A fix for case 2 would split the WHERE into inlinable (user filters) and non-inlinable (InSubquery) parts. Deferred — only matters for multi-rel queries, and ClickHouse 26.3 materialized CTEs make it moot. ### Edge table re-scans | # | Pattern | Passes | Status | |---|---|---|---| | 3 | `_cascade_*` + main edge scan | optimize + lower | Waiting on ClickHouse 26.3 | | 4 | `narrow_joined_nodes` + main edge scan | optimize + lower | Waiting on ClickHouse 26.3 | | 5 | `cascade_node_filter_ctes` + main edge scan | optimize + lower | Waiting on ClickHouse 26.3 | | 7 | Hop frontier CTEs + UNION ALL arms | optimize + lower | Waiting on ClickHouse 26.3 | These are inherent to CTE inlining. Each cascade/frontier CTE independently scans `gl_edge`, duplicating work the main FROM tree also does. Worst case: 24 edge scans for a 3-node multi-hop query. ### Already handled / not actionable | # | Pattern | Status | |---|---|---| | 2 | Root SIP duplicating `_nf_*` | Already deduplicated (optimize.rs:550-551 reuses existing `_nf_*`) | | 9 | Security `startsWith` on every scan | Inherent to security model. PathTrie (!1116) minimizes predicate count. PK prefix handles efficiently. | | 10 | Cascade injected into `_nf_*` | Not same-table duplication (edge → node cross-reference) | ## Fix for patterns 3-7: ClickHouse 26.3 materialized CTEs ClickHouse 26.3 adds [materialized CTEs](https://clickhouse.com/blog/clickhouse-release-26-03#materialized-cte) which compute CTE results once and reuse them across references. When 26.3 is live, patterns 3-7 can be fixed by adding `MATERIALIZED` hints to cascade, SIP, hop frontier, and security-scoped CTEs. This is a codegen-only change in `clickhouse.rs` — the AST `Cte` struct already has a `materialized: bool` field that just needs to be plumbed. ## Context Identified during eval harness profiling (Q3: 3-node aggregation on Definition, 366M rows read for a 44.5M row table). Related MRs: !1104 (hop frontiers), !1106 (cascade CTEs), !1114 (extend to all query types), !1117 (skip indexes), !1119 (dedup inlining).