perf(compiler): replace FilterOnly CTE+IN with INNER JOIN for edge chains

What does this MR do and why?

Part of #813 (closed). FilterOnly nodes in flat-chain queries previously used a CTE to pre-scan the node table with FINAL, then filtered edges via IN (SELECT id FROM _filter_cte). Now they use the same INNER JOIN + LIMIT BY path as Join nodes, but without projecting columns into the SELECT.

Profiling shows a 70% reduction in read_rows and 72% reduction in read_bytes on code-graph queries (File→Definition with path/name filters). SDLC queries are neutral (small tables where CTE overhead is negligible).

Testing

All 321 compiler unit tests pass. Two tests updated to assert JOIN instead of CTE.

Performance Analysis

Query read_rows read_bytes elapsed
File→Definition (path+name filters) -70% -72% -31%
Note internal filter same same -20%
MR source_branch/title filters same same same

The File→Definition win comes from eliminating a double-scan: baseline scanned each node table twice (once for the CTE, once for the JOIN). Now each table is scanned once via the JOIN.

  • This merge request does not introduce any performance regression. If a performance regression is expected, explain why.
Agent context

The change converts HydrationStrategy::FilterOnly and elevated-access Skip nodes from emit_filter_subquery (CTE + FINAL + IN) to emit_node_join_with_narrowing (INNER JOIN + LIMIT BY). The security pass already recurses into JOIN subqueries via apply_security_to_from, so traversal_path filters are injected correctly without the CTE.

FK-star queries retain CTEs for now (different structure in fk_star.rs).

Baseline SQL (query 04):

WITH _filter_f AS (SELECT f.id FROM gl_file FINAL AS f WHERE endsWith(f.path, '.rb') ...),
     _filter_d AS (SELECT d.id FROM gl_definition FINAL AS d WHERE startsWith(d.name, 'process') ...)
SELECT ... FROM gl_code_edge e0
INNER JOIN (SELECT * FROM gl_file FINAL AS f WHERE ... ) AS f ON e0.source_id = f.id
INNER JOIN (SELECT * FROM gl_definition FINAL AS d WHERE ...) AS d ON e0.target_id = d.id
WHERE e0.source_id IN (SELECT id FROM _filter_f)
  AND e0.target_id IN (SELECT id FROM _filter_d)

Each node table was scanned twice: once for the CTE (FINAL), once for the JOIN (FINAL). The CTE materialized all matching IDs, then the IN predicate filtered edges, then the JOIN re-scanned the same table.

After:

SELECT ... FROM gl_code_edge e0
INNER JOIN (SELECT * FROM gl_file AS f WHERE ... ORDER BY ... LIMIT 1 BY ...) AS f ON f.id = e0.source_id
INNER JOIN (SELECT * FROM gl_definition AS d WHERE ... ORDER BY ... LIMIT 1 BY ...) AS d ON d.id = e0.target_id
WHERE ...

Each node table scanned once. LIMIT BY replaces FINAL. No CTEs.

%{all_commits}

Edited by Michael Usachenko

Merge request reports

Loading
Loading