### Problem to Solve Auditing the in-repo query fixture corpus under `fixtures/queries/` (295 queries across 8 files) surfaces a small number of compiler and schema patterns that account for the bulk of execution cost. Two themes dominate the long tail, and most of the rest reduce to the same root cause: filters and predicates that the planner cannot push deep enough into ClickHouse to enable granule pruning. ### Proposed Solution Land focused fixes for the recurring patterns, in roughly cheapest-to-largest order. Two of these are queued behind upcoming MRs. - [x] Push edge predicates into the inner WHERE of per-hop dedup scans so KeyCondition sees them, not just the outer projection wrapper (MR: !1519). - [x] Add an explicit minmax skip index on Nullable temporal columns on `gl_job` and equivalents, since ClickHouse's auto-minmax pass skips Nullable types (MR: !1520). - [ ] Stop emitting unconditional `FROM table FINAL` from `Strategy::SingleNode`. FINAL on ReplacingMergeTree node tables under a wide auth prefix is the largest single source of wasted I/O in the corpus and silently disables projections. - [ ] Reuse the per-hop edge dedup scan across cascade and reverse-cascade injections so the auth prefix is carried into the inner WHERE once, not duplicated per CTE. - [ ] Replace `contains(...)` and `endsWith(...)` filters on indexed string columns with prefix or anchored forms the PK can prune. - [ ] Route path-finding queries through the existing SIP and cascade-SIP passes; today they bypass every narrowing step and scan unbounded. - [ ] Confirm projections (`by_source`, `by_target`, `by_source_kind`) are picked by the optimizer when the WHERE uses the appropriate edge column; the corpus shows several queries where the projection should fire and does not. <details> <summary><b>Agent context</b> — extended analysis, alternatives considered, related-code dumps</summary> Audit method: every query in `fixtures/queries/corpus-input.json`, `fixtures/queries/sdlc_queries.yaml`, `fixtures/queries/code_graph_queries.yaml`, `fixtures/queries/optimization_benchmarks.yaml`, `fixtures/queries/optimization_showcase.json`, `fixtures/queries/property_grouping.json`, `fixtures/queries/property_grouping_stress.json`, and `fixtures/queries/cursor_profiling.json` was compiled, executed, and profiled. Aggregates below are corpus-share percentages over the 295 queries; absolute timings and row counts are deliberately omitted. Theme distribution (share of total execution cost across the corpus): - FINAL on ReplacingMergeTree node tables under a wide auth prefix: ~31%. Triggered from `Strategy::SingleNode` in the compiler when the query reduces to a single node scan plus auth context. FINAL serializes the merge on the read path and disables projections. - Per-hop dedup CTE carries only the auth prefix in its inner WHERE: ~22%. Edge predicates (relationship_kind, target-kind constraints) sit on the outer wrapper, so KeyCondition cannot prune granules and the scan widens to the full prefix. - Path-finding bypasses every SIP pass: ~12%. `path_finding` lowers without `apply_sip_prefilter`, `apply_cascade_sip`, or `apply_limit_pushdown` firing, so the root CTE materializes unboundedly. - Nullable temporal columns get no auto-minmax: ~9%. ClickHouse's `add_minmax_index_for_temporal_columns` does not unwrap Nullable, so `gl_job.finished_at` and similar columns get no granule-level pruning even when the filter is highly selective. - Edge-only aggregation eligible but not applied: ~6%. `apply_edge_only_aggregation` fails to fire when the root has display columns the formatter does not actually emit; the root table stays in the FROM and adds a redundant join. - contains() / endsWith() on indexed string columns: ~5%. Both are unindexed in ClickHouse; the optimizer should rewrite to anchored forms (or startsWith for the path case) where the predicate semantics allow. - JOIN ... USING on mixed-nullable columns forces materialization: ~4%. Nullable on one side of the join key disables sort-merge and falls back to hash with a build-side scan of the wide table. - Cascade CTE LIMIT not propagated for aggregation queries: ~4%. Aggregation correctness blocks `propagate_limit_to_cascade_ctes`, but a handful of corpus queries are LIMIT-aggregations (top-N by count) and could safely cap. - traversal_path filter not rewritten to a PK prefix: ~3%. `rewrite_path_filters` is still gated behind a flag in some lowering paths; explicit project or group filters remain on `project_id` and never become a `startsWith(traversal_path, ...)` PK probe. - Virtual column resolution forces a re-scan: ~2%. `ColumnResolverRegistry` resolves a small number of virtual columns by joining back to the source table after the aggregation has already collapsed it. - Projection eligibility miss on `by_source_kind`: ~2%. The corpus has several namespace-scoped queries where `(traversal_path, source_kind, relationship_kind, ...)` would dominate the main PK; the optimizer does not select it. The remaining ~0.7% is long-tail noise. Why minmax and edge-predicate pushdown are the two cheapest large wins: - Minmax on Nullable temporal columns is a one-line DDL change per column plus a `SCHEMA_VERSION` bump. No compiler change. The granule pruning on time-bounded queries is large because most pipeline and job queries in the corpus are time-bounded. - Pushing edge predicates into the inner WHERE of the per-hop dedup scan is a localized compiler change in the shared dedup-scan helper used by `apply_sip_prefilter` and the cascade passes. It does not require a schema change, does not interact with FINAL, and unlocks KeyCondition on the existing PK and projections. References in tree: - Strategy::SingleNode FINAL: `crates/query-engine/compiler/src/lower/strategy.rs`. - Per-hop dedup scan: `crates/query-engine/compiler/src/optimize/dedup_edge_scan.rs`. - SIP / cascade SIP: `crates/query-engine/compiler/src/optimize/sip.rs`, `crates/query-engine/compiler/src/optimize/cascade.rs`. - Path-finding lowering: `crates/query-engine/compiler/src/lower/path_finding.rs`. - Schema DDL: `config/graph.sql`, version pin in `config/SCHEMA_VERSION`. - Fixtures driving this audit: `fixtures/queries/`. </details> <!-- AI-Sessions dir: ~/.claude/projects/-Users-angelo-rivera-gitlab-orbit-knowledge-graph/ bf26f0e8-e72c-40c4-a7e7-30a32f55e310.jsonl (2026-06-03) -->