perf(schema): add _deleted to code graph RMT, projections on MR and Note, granularity 1024

Michael Usachenkorequested to merge
perf/ddl-code-graph-cleanup-and-projections into main

Summary

  • Remove version_only_engine from gl_definition, gl_file, gl_directory, gl_imported_symbol so ReplacingMergeTree uses (_version, _deleted) and background merges garbage-collect deleted rows. Dead code graph rows currently accumulate forever.
  • Add by_project_state projection on gl_merge_request — ORDER BY (traversal_path, project_id, state, id) — collapses cascade CTE + dedup into a single projection range scan for "MRs in project X with state Y."
  • Add by_noteable projection on gl_note — ORDER BY (traversal_path, noteable_type, noteable_id, id) — clusters notes by parent entity for HAS_NOTE traversals and per-MR aggregations.
  • Set index_granularity=1024 on all 4 code graph node tables (was 2048) to double bloom/text skip index granule resolution.

Production perf test queries addressed

Change Cluster Queries
_deleted on code graph RMT C2 (code graph wall) Q1,Q21,Q26,Q32,G1 (5 timeouts); Q22,Q31,G3 (3 slow)
by_project_state projection C1,C3 (dense table + 3-node agg) Q4(8.5s), F2(9.8s), I5(11.2s), Q6(6.6s), E2(5.9s)
by_noteable projection C1 (dense table) I5(11.2s), A1(11.4s)
index_granularity=1024 C2 (code graph wall) All code graph queries

Relates to #544

Merge request reports