gabriel / musehub public
test_query_plans.py python
308 lines 18.5 KB
Raw
sha256:c3c5945f4af49c55c1e625b8d7d33065ceca41cd6ac991101c44fd61ca6a09c4 test: land rc22 tests on dev's parallel harness (psycopg2 d… Opus 5.5 2 days ago
1 """Query-plan regression tests: the hot read paths must be index-served and bounded.
2
3 A throwaway database is created on the test server, the ORM schema (with every declared index) is created, big tables are seeded with ``generate_series`` at
4 production-like skew (a few large repositories among many small), ``ANALYZE``d, and the *very statements the routes execute* (``musehub.services.intel_queries``)
5 are ``EXPLAIN``ed. Each must avoid a sequential scan of the big tables and stay under a plan-cost ceiling that a seq scan of the seeded data would exceed
6 many times over. The collation is ``en_US.UTF-8`` as in production: a plain btree cannot serve ``LIKE 'prefix%'`` there, which is the regression that motivated this file.
7 """
8 from __future__ import annotations
9
10 import os
11 import re
12 import uuid
13 from collections.abc import Iterator
14
15 import pytest
16 import sqlalchemy as sa
17 from sqlalchemy.dialects import postgresql
18 from sqlalchemy import Connection, create_engine
19 from sqlalchemy.pool import NullPool
20
21 import musehub.main # noqa: F401 (imports every ORM model)
22 from musehub.db.database import Base
23 from musehub.db.musehub_intel_models import MusehubSymbolHistoryEntry as H
24 from musehub.services import intel_queries as Q
25
26 _URL = os.environ.get("TEST_DATABASE_URL", "postgresql+asyncpg://musehub:musehub@localhost:5434/musehub_test").replace("+asyncpg", "+psycopg2")
27 _ADMIN = re.sub(r"/[^/]+$", "/postgres", _URL)
28 BIG = "sha256:" + "a" * 64 # a large repository: 40k symbols
29 OTHERS = [f"sha256:{i:064x}" for i in range(1, 41)]
30 BIG_TABLES = {"musehub_symbol_intel", "musehub_intel_type", "musehub_intel_api_surface", "musehub_symbol_history_entries", "musehub_intel_stable", "musehub_intel_dead", "musehub_symbol_vitals", "musehub_intel_blast_risk"}
31 _SEQ_COST: dict[str, float] = {} # cost of reading each whole big table, measured on the seeded database
32
33
34 @pytest.fixture(scope="module")
35 def conn() -> Iterator[Connection]:
36 name = f"musehub_plans_{uuid.uuid4().hex[:10]}"
37 admin = create_engine(_ADMIN, poolclass=NullPool, isolation_level="AUTOCOMMIT")
38 with admin.connect() as a:
39 a.execute(sa.text(f'CREATE DATABASE "{name}" LC_COLLATE "en_US.UTF-8" LC_CTYPE "en_US.UTF-8" TEMPLATE template0'))
40 eng = create_engine(re.sub(r"/[^/]+$", f"/{name}", _URL), poolclass=NullPool)
41 try:
42 with eng.begin() as c:
43 Base.metadata.create_all(c)
44 c.execute(sa.text("SET session_replication_role = replica")) # seed without the repo/FK graph
45 _seed(c)
46 with eng.connect().execution_options(isolation_level="AUTOCOMMIT") as v:
47 v.execute(sa.text("VACUUM ANALYZE")) # the visibility map is what makes index-only scans possible
48 with eng.connect() as c:
49 for t in BIG_TABLES:
50 _SEQ_COST[t] = float(re.search(r"cost=[\d.]+\.\.([\d.]+)", c.execute(sa.text(f"EXPLAIN SELECT * FROM {t}")).first()[0]).group(1))
51 yield c
52 finally:
53 eng.dispose()
54 with admin.connect() as a:
55 a.execute(sa.text(f'DROP DATABASE IF EXISTS "{name}" WITH (FORCE)'))
56 admin.dispose()
57
58
59 def _seed(c: Connection) -> None:
60 repos = [(BIG, 40000)] + [(r, 2000) for r in OTHERS] # the big repository is ~1/3 of each table, as on staging
61 for rid, n in repos:
62 p = {"r": rid, "n": n}
63 c.execute(sa.text(
64 "INSERT INTO musehub_symbol_intel (repo_id,address,op,churn,churn_30d,churn_90d,blast,blast_cross,blast_direct,blast_top,gravity,author_count,weekly) "
65 "SELECT :r, 'pkg/m' || (i % 400) || '.py::sym' || i, (ARRAY['insert','mutate','replace','move'])[1 + i % 4], i % 50, (i * 7) % 30, i % 90, (i * 13) % 100, 0, 0, '{}', 0.0, 1, '{}' "
66 "FROM generate_series(1, :n) i"), p)
67 c.execute(sa.text(
68 "INSERT INTO musehub_intel_type (repo_id,address,kind,params_annotated,params_total,params_with_any,ref,type_score) "
69 "SELECT :r, 'pkg/m' || (i % 400) || '.py::sym' || i, (ARRAY['function','method','class'])[1 + i % 3], 1, 2, i % 3, 'main', (i % 11) / 10.0 FROM generate_series(1, :n) i"), p)
70 c.execute(sa.text(
71 "INSERT INTO musehub_intel_stable (repo_id,address,days_stable,since_start,ref) "
72 "SELECT :r, 'pkg/m' || (i % 400) || '.py::sym' || i, i % 900, (i % 7 = 0), 'main' FROM generate_series(1, :n) i"), p)
73 c.execute(sa.text(
74 "INSERT INTO musehub_intel_dead (repo_id,address,kind,confidence,ref,dismissed) "
75 "SELECT :r, 'pkg/m' || (i % 400) || '.py::sym' || i, 'function', (ARRAY['high','medium','low'])[1 + i % 3], 'main', (i % 10 = 0) "
76 "FROM generate_series(1, :n / 4) i"), p)
77 c.execute(sa.text(
78 "INSERT INTO musehub_symbol_vitals (repo_id,address,change_count,op_add,op_delete,op_modify,op_move,version_count) "
79 "SELECT :r, 'pkg/m' || (i % 400) || '.py::sym' || i, i % 50, 1, 0, 1, 0, 1 FROM generate_series(1, :n) i"), p)
80 c.execute(sa.text(
81 "INSERT INTO musehub_intel_blast_risk (repo_id,address,kind,risk,risk_score,impact_score,churn_score,test_gap_score,coupling_score,ref) "
82 "SELECT :r, 'pkg/m' || (i % 400) || '.py::sym' || i, 'function', 'low', i % 100, 0, 0, 0, 0, 'main' FROM generate_series(1, :n) i"), p)
83 c.execute(sa.text(
84 "INSERT INTO musehub_intel_api_surface (repo_id,address,kind,ref) "
85 "SELECT :r, 'pkg/m' || (i % 400) || '.py::sym' || i, (ARRAY['function','method','class'])[1 + i % 3], 'main' FROM generate_series(1, :n) i"), p)
86 c.execute(sa.text(
87 "INSERT INTO musehub_symbol_history_entries (repo_id,address,commit_id,committed_at,op) "
88 "SELECT :r, 'pkg/m' || (i % 400) || '.py' || (CASE WHEN i % 3 = 0 THEN '::sym' || i ELSE '' END), 'sha256:' || md5(i::text || :r), now() - (i || ' minutes')::interval, 'mutate' "
89 "FROM generate_series(1, :n * 2) i"), p)
90
91
92 def _plan(c: Connection, stmt: sa.sql.ClauseElement) -> tuple[str, float]:
93 compiled = stmt.compile(dialect=postgresql.dialect(), compile_kwargs={"literal_binds": True})
94 rows = (c.execute(sa.text("EXPLAIN " + str(compiled).replace(":", "\\:")))).all()
95 text = "\n".join(r[0] for r in rows)
96 return text, float(re.search(r"cost=[\d.]+\.\.([\d.]+)", text).group(1))
97
98
99 def _assert_indexed(plan: str, cost: float, label: str) -> None:
100 """No sequential scan of a big table, and at most a third of what reading the whole table would cost (relative, so planner estimates cannot flake it)."""
101 seq = [t for t in re.findall(r"Seq Scan on (\w+)", plan) if t in BIG_TABLES]
102 assert not seq, f"{label}: sequential scan of {seq}\n{plan}"
103 tables = [t for t in re.findall(r" on (\w+)", plan) if t in BIG_TABLES] or list(BIG_TABLES)
104 ceiling = min(_SEQ_COST[t] for t in tables) / 3
105 assert cost <= ceiling, f"{label}: plan cost {cost:.0f} > {ceiling:.0f} (1/3 of a full scan)\n{plan}"
106
107
108 def test_file_history_lookup_uses_the_pattern_index(conn: Connection) -> None:
109 stmt = sa.select(H).where(Q.file_history_predicate(H, BIG, "pkg/m7.py")).order_by(sa.desc(H.committed_at)).limit(1)
110 plan, cost = _plan(conn, stmt)
111 _assert_indexed(plan, cost, "file history")
112 assert "ix_symbol_history_repo_address_pattern" in plan
113
114
115 def test_explore_top_symbol_per_repo_is_one_index_probe_per_repo(conn: Connection) -> None:
116 for by in ("churn_30d", "blast"):
117 plan, cost = _plan(conn, Q.explore_top_symbol_stmt([BIG, *OTHERS[:20]], by=by))
118 _assert_indexed(plan, cost, f"explore top by {by}")
119 assert "Index" in plan
120
121
122 def test_explore_top_symbol_returns_the_true_maximum(conn: Connection) -> None:
123 rows = (conn.execute(Q.explore_top_symbol_stmt([BIG, OTHERS[0]], by="churn_30d"))).all()
124 assert {r.repo_id for r in rows} == {BIG, OTHERS[0]}
125 for r in rows:
126 best = (conn.execute(sa.text("SELECT max(churn_30d) FROM musehub_symbol_intel WHERE repo_id = :r"), {"r": r.repo_id})).scalar_one()
127 assert r.churn_30d == best
128 assert conn.execute(Q.explore_top_symbol_stmt([BIG], by="blast")).one().blast == 99
129
130
131 def test_type_summary_is_one_pass_and_matches_the_five_counts(conn: Connection) -> None:
132 summary = Q.summarise_types(conn.execute(Q.type_summary_stmt(BIG)).all())
133 expect = {
134 "total": "SELECT count(*) FROM musehub_intel_type WHERE repo_id = :r",
135 "fully_typed": "SELECT count(*) FROM musehub_intel_type WHERE repo_id = :r AND type_score = 1.0",
136 "partially_typed": "SELECT count(*) FROM musehub_intel_type WHERE repo_id = :r AND type_score >= 0.5 AND type_score < 1.0",
137 "untyped": "SELECT count(*) FROM musehub_intel_type WHERE repo_id = :r AND type_score < 0.5",
138 "any_count": "SELECT count(*) FROM musehub_intel_type WHERE repo_id = :r AND (return_is_any IS true OR params_with_any > 0)",
139 }
140 for field, sql in expect.items():
141 assert getattr(summary, field) == (conn.execute(sa.text(sql), {"r": BIG})).scalar_one(), field
142 assert sorted(summary.kinds) == ["class", "function", "method"]
143 plan, _ = _plan(conn, Q.type_summary_stmt(BIG))
144 assert plan.count("on musehub_intel_type") == 1 and "Sort" not in plan, plan # one pass, not six; no sort
145
146
147 def test_type_page_list_reads_n_index_entries(conn: Connection) -> None:
148 from musehub.db.musehub_intel_models import MusehubIntelType as T
149 stmt = sa.select(T).where(T.repo_id == BIG, T.type_score < 0.5).order_by(T.type_score.asc(), T.address.asc()).limit(20)
150 plan, cost = _plan(conn, stmt)
151 _assert_indexed(plan, cost, "type list")
152 assert "ix_intel_type_repo_score_addr" in plan and "Sort" not in plan
153
154
155 def test_api_surface_kind_counts_are_index_only(conn: Connection) -> None:
156 plan, _ = _plan(conn, Q.api_surface_kind_counts_stmt(BIG))
157 assert "Index Only Scan using ix_intel_api_surface_repo_kind" in plan, plan
158 rows = {r.kind: r.cnt for r in (conn.execute(Q.api_surface_kind_counts_stmt(BIG))).all()}
159 assert sum(rows.values()) == 40000 and set(rows) == {"function", "method", "class"}
160
161
162 def test_symbols_page_count_by_op_is_index_only(conn: Connection) -> None:
163 from musehub.db.musehub_intel_models import MusehubSymbolIntel as S
164 plan, _ = _plan(conn, sa.select(S.op, sa.func.count()).where(S.repo_id == BIG).group_by(S.op))
165 assert "Index Only Scan using ix_symbol_intel_repo_op" in plan, plan
166
167
168 # ── Controls: the harness must flag the patterns this file replaced (otherwise a green run proves nothing) ─────────────────────────────────────────────────────────────
169
170
171 def test_control_old_window_function_is_a_seq_scan(conn: Connection) -> None:
172 from musehub.db.musehub_intel_models import MusehubSymbolIntel as S
173 rn = sa.func.row_number().over(partition_by=S.repo_id, order_by=S.churn_30d.desc().nulls_last()).label("rn")
174 inner = sa.select(S.repo_id, S.address, rn).where(S.repo_id.in_([BIG, *OTHERS[:20]]), S.churn_30d > 0).subquery()
175 plan, cost = _plan(conn, sa.select(inner).where(inner.c.rn == 1))
176 assert "Seq Scan on musehub_symbol_intel" in plan or cost > _SEQ_COST["musehub_symbol_intel"] / 5, plan
177
178
179 def test_control_prefix_lookup_without_the_pattern_index_is_a_seq_scan(conn: Connection) -> None:
180 stmt = sa.select(H).where(Q.file_history_predicate(H, BIG, "pkg/m7.py")).order_by(sa.desc(H.committed_at)).limit(1)
181 sp = conn.begin_nested()
182 try:
183 conn.execute(sa.text("DROP INDEX ix_symbol_history_repo_address_pattern"))
184 plan, cost = _plan(conn, stmt)
185 finally:
186 sp.rollback()
187 assert "Seq Scan on musehub_symbol_history_entries" in plan or cost > _SEQ_COST["musehub_symbol_history_entries"] / 5, plan
188
189
190
191 def test_file_history_predicate_escapes_like_wildcards_and_keeps_the_index(conn: Connection) -> None:
192 """``a_b.py`` must not match ``aXb.py``'s symbols; a path starting with ``_`` must still use the pattern index (it has a literal prefix once escaped)."""
193 sp = conn.begin_nested()
194 try:
195 conn.execute(sa.text("SET LOCAL session_replication_role = replica"))
196 conn.execute(sa.text(
197 "INSERT INTO musehub_symbol_history_entries (repo_id,address,commit_id,committed_at,op) VALUES "
198 "(:r,'pkg/aXb.py::f','sha256:x1',now(),'mutate'), (:r,'pkg/a_b.py::g','sha256:x2',now(),'mutate'), (:r,'_private.py::h','sha256:x3',now(),'mutate')"),
199 {"r": OTHERS[0]})
200 got = conn.execute(sa.select(H.address).where(Q.file_history_predicate(H, OTHERS[0], "pkg/a_b.py"))).scalars().all()
201 assert got == ["pkg/a_b.py::g"]
202 plan, cost = _plan(conn, sa.select(H).where(Q.file_history_predicate(H, BIG, "_private.py")).order_by(sa.desc(H.committed_at)).limit(1))
203 _assert_indexed(plan, cost, "underscore path")
204 finally:
205 sp.rollback()
206
207
208 def test_explore_lateral_runs_through_asyncpg(conn: Connection) -> None:
209 """The production driver binds VALUES parameters itself (psycopg2 + literal binds above never exercises that path)."""
210 import asyncio
211 from sqlalchemy.ext.asyncio import create_async_engine as _cae
212
213 url = conn.engine.url.set(drivername="postgresql+asyncpg")
214
215 async def run() -> list[tuple[str, int]]:
216 eng = _cae(url, poolclass=NullPool)
217 try:
218 async with eng.connect() as c:
219 return [(r.repo_id, r.churn_30d) for r in (await c.execute(Q.explore_top_symbol_stmt([BIG, OTHERS[1]], by="churn_30d"))).all()]
220 finally:
221 await eng.dispose()
222
223 rows = dict(asyncio.run(run()))
224 assert set(rows) == {BIG, OTHERS[1]} and all(v == 29 for v in rows.values())
225
226
227 def test_control_old_type_page_six_scans_cost_far_more(conn: Connection) -> None:
228 from musehub.db.musehub_intel_models import MusehubIntelType as T
229 old = [
230 sa.select(sa.func.count()).select_from(T).where(T.repo_id == BIG),
231 sa.select(sa.func.count()).select_from(T).where(T.repo_id == BIG, T.type_score == 1.0),
232 sa.select(sa.func.count()).select_from(T).where(T.repo_id == BIG, T.type_score >= 0.5, T.type_score < 1.0),
233 sa.select(sa.func.count()).select_from(T).where(T.repo_id == BIG, T.type_score < 0.5),
234 sa.select(sa.func.count()).select_from(T).where(T.repo_id == BIG, sa.or_(T.return_is_any.is_(True), T.params_with_any > 0)),
235 sa.select(T.kind).distinct().where(T.repo_id == BIG).order_by(T.kind),
236 ]
237 old_cost = sum(_plan(conn, q)[1] for q in old)
238 _plan_text, new_cost = _plan(conn, Q.type_summary_stmt(BIG))
239 assert old_cost > 2.5 * new_cost, (old_cost, new_cost) # six passes -> one (both index-served now)
240 assert "Index Only Scan using ix_intel_type_repo_summary" in _plan_text, _plan_text
241
242
243
244 def test_symbols_search_uses_the_trigram_index(conn: Connection) -> None:
245 from musehub.db.musehub_intel_models import MusehubSymbolIntel as S
246 from musehub.db.utils import escape_like
247 stmt = sa.select(S.op, sa.func.count()).where(S.repo_id == BIG, S.address.ilike(f"%{escape_like('m17.py::sym4')}%")).group_by(S.op)
248 plan, cost = _plan(conn, stmt)
249 seq_cost = _plan(conn, sa.select(sa.func.count()).select_from(S))[1]
250 assert "ix_symbol_intel_address_trgm" in plan and "Seq Scan" not in plan, plan
251 assert cost < seq_cost / 2, (cost, seq_cost)
252
253
254
255 def test_stable_stats_are_one_index_only_pass(conn: Connection) -> None:
256 stats = conn.execute(Q.stable_stats_stmt(BIG)).one()
257 r = {"r": BIG}
258 assert stats.total == conn.execute(sa.text("SELECT count(*) FROM musehub_intel_stable WHERE repo_id = :r"), r).scalar_one()
259 assert stats.eternal == conn.execute(sa.text("SELECT count(*) FROM musehub_intel_stable WHERE repo_id = :r AND since_start"), r).scalar_one()
260 assert stats.veteran == conn.execute(sa.text("SELECT count(*) FROM musehub_intel_stable WHERE repo_id = :r AND NOT since_start AND days_stable >= 365"), r).scalar_one()
261 assert stats.max_days == 899
262 plan, _cost = _plan(conn, Q.stable_stats_stmt(BIG))
263 # Counting a third of the table is O(rows) whatever the plan: assert ONE pass and no sequential scan of the whole table (the page used to load every
264 # stable row as an ORM object, twice per dashboard visit, and count in Python). Routes cache this aggregate for a minute (it changes on analysis only).
265 assert plan.count("on musehub_intel_stable") == 1 and "Seq Scan" not in plan, plan
266
267
268 def test_stable_top_n_reads_n_entries(conn: Connection) -> None:
269 from musehub.db.musehub_intel_models import MusehubIntelStable as T
270 plan, cost = _plan(conn, sa.select(T).where(T.repo_id == BIG).order_by(sa.desc(T.days_stable)).limit(50))
271 assert "ix_intel_stable_repo_days" in plan and "Sort" not in plan, plan
272 _assert_indexed(plan, cost, "stable top-n")
273
274
275 def test_dead_stats_and_tiers(conn: Connection) -> None:
276 stats = conn.execute(Q.dead_stats_stmt(BIG)).one()
277 r = {"r": BIG}
278 assert stats.active == conn.execute(sa.text("SELECT count(*) FROM musehub_intel_dead WHERE repo_id = :r AND NOT dismissed"), r).scalar_one()
279 assert stats.high_all == conn.execute(sa.text("SELECT count(*) FROM musehub_intel_dead WHERE repo_id = :r AND confidence = 'high'"), r).scalar_one()
280 plan, cost = _plan(conn, Q.dead_stats_stmt(BIG))
281 assert "Index Only Scan using ix_intel_dead_repo_cover" in plan, plan
282 rows = conn.execute(Q.dead_tier_rows_stmt(BIG, "high", include_dismissed=False, limit=200)).all()
283 assert len(rows) == 200 and all(x.confidence == "high" and not x.dismissed for x in rows)
284 assert [x.address for x in rows] == sorted(x.address for x in rows)
285 plan, cost = _plan(conn, Q.dead_tier_rows_stmt(BIG, "high", include_dismissed=False, limit=200))
286 assert "ix_intel_dead_repo_active" in plan and "Sort" not in plan, plan
287 _assert_indexed(plan, cost, "dead tier")
288
289
290 def test_symbols_list_page_reads_one_page(conn: Connection) -> None:
291 plan, cost = _plan(conn, Q.symbol_list_stmt(BIG, q=None, op=[], offset=0, limit=50))
292 assert "ix_symbol_intel_repo_churn_list" in plan and "Sort" not in plan, plan
293 _assert_indexed(plan, cost, "symbols list")
294
295
296
297 def test_blast_risk_top_n_reads_n_entries(conn: Connection) -> None:
298 from musehub.db.musehub_intel_models import MusehubIntelBlastRisk as B
299 plan, cost = _plan(conn, sa.select(B).where(B.repo_id == BIG).order_by(sa.desc(B.risk_score)).limit(10))
300 assert "ix_intel_blast_risk_repo_score" in plan and "Sort" not in plan, plan
301 _assert_indexed(plan, cost, "blast risk top-n")
302
303
304 def test_api_surface_list_reads_n_entries(conn: Connection) -> None:
305 from musehub.db.musehub_intel_models import MusehubIntelApiSurface as A
306 plan, cost = _plan(conn, sa.select(A).where(A.repo_id == BIG).order_by(A.kind.asc(), A.address.asc()).limit(20))
307 assert "ix_intel_api_surface_repo_kind_addr" in plan and "Sort" not in plan, plan
308 _assert_indexed(plan, cost, "api surface list")
File History 3 commits
sha256:c3c5945f4af49c55c1e625b8d7d33065ceca41cd6ac991101c44fd61ca6a09c4 test: land rc22 tests on dev's parallel harness (psycopg2 d… Opus 5.5 2 days ago
sha256:30c51e90dfe2486f79752f7305be1faf2f086d5407ee5e1307167b5a8c61549d perf(db): SQL audit pass 2 + Tier A review fixes; local tim… Opus 5.5 minor ⚠ 2 days ago
sha256:93c33b03c434d9887a5d4ee3a9329c9a126343e713a28173ca4c10d85f41eef8 perf(db): SQL audit pass 1 — hot read paths index-served, o… Opus 5.5 minor ⚠ 2 days ago