test_proposal_list_phase7.py
python
sha256:34035d72cef530c1ab9d6a6f53be18d803dde705fc3157617d70352a96d0747b
Merge branch 'fix/wire-push-external-parent-manifest' into dev
Human
8 days ago
| 1 | """Phase 7 — Index optimization test harness for issue #35. |
| 2 | |
| 3 | Tier 6 — Performance |
| 4 | Verifies that the 4 composite indexes added in migration 0044 are actually |
| 5 | used by the PostgreSQL query planner for the proposal list query patterns. |
| 6 | |
| 7 | Each test runs EXPLAIN (FORMAT JSON, ANALYZE FALSE) — no rows are executed, |
| 8 | so there are no timing side-effects. We assert that the plan contains an |
| 9 | "Index Scan" or "Bitmap Index Scan" node on the expected index name, proving |
| 10 | that the planner will choose the index rather than a seq scan. |
| 11 | |
| 12 | Not a correctness test — correctness is covered by Phase 1–3 tests. |
| 13 | Not a latency test — CI hardware is too variable for absolute thresholds. |
| 14 | """ |
| 15 | |
| 16 | from __future__ import annotations |
| 17 | |
| 18 | import json |
| 19 | import uuid |
| 20 | from datetime import datetime, timezone |
| 21 | |
| 22 | import pytest |
| 23 | from sqlalchemy import text |
| 24 | from sqlalchemy.ext.asyncio import AsyncSession |
| 25 | |
| 26 | from musehub.types.json_types import JSONValue, StrDict |
| 27 | |
| 28 | |
| 29 | # ───────────────────────────────────────────────────────────────────────────── |
| 30 | # Helpers |
| 31 | # ───────────────────────────────────────────────────────────────────────────── |
| 32 | |
| 33 | |
| 34 | def _now() -> datetime: |
| 35 | return datetime.now(tz=timezone.utc) |
| 36 | |
| 37 | |
| 38 | async def _make_repo(session: AsyncSession) -> str: |
| 39 | from musehub.core.genesis import compute_identity_id, compute_repo_id |
| 40 | owner = "p7user" |
| 41 | slug = f"repo-{uuid.uuid4().hex[:8]}" |
| 42 | owner_id = compute_identity_id(owner.encode()) |
| 43 | created_at = _now() |
| 44 | from musehub.db.musehub_repo_models import MusehubRepo |
| 45 | repo = MusehubRepo( |
| 46 | repo_id=compute_repo_id(owner_id, slug, "code", created_at.isoformat()), |
| 47 | name=slug, |
| 48 | owner=owner, |
| 49 | slug=slug, |
| 50 | visibility="public", |
| 51 | owner_user_id=owner_id, |
| 52 | description="", |
| 53 | tags=[], |
| 54 | created_at=created_at, |
| 55 | ) |
| 56 | session.add(repo) |
| 57 | await session.commit() |
| 58 | return repo.repo_id |
| 59 | |
| 60 | |
| 61 | def _plan_nodes(plan: JSONValue) -> list[str]: |
| 62 | """Recursively collect all 'Node Type' values from a EXPLAIN JSON plan.""" |
| 63 | nodes: list[str] = [] |
| 64 | if isinstance(plan, dict): |
| 65 | if "Node Type" in plan: |
| 66 | nodes.append(plan["Node Type"]) |
| 67 | for v in plan.values(): |
| 68 | nodes.extend(_plan_nodes(v)) |
| 69 | elif isinstance(plan, list): |
| 70 | for item in plan: |
| 71 | nodes.extend(_plan_nodes(item)) |
| 72 | return nodes |
| 73 | |
| 74 | |
| 75 | def _plan_indexes(plan: JSONValue) -> list[str]: |
| 76 | """Recursively collect all 'Index Name' values from a EXPLAIN JSON plan.""" |
| 77 | indexes: list[str] = [] |
| 78 | if isinstance(plan, dict): |
| 79 | if "Index Name" in plan: |
| 80 | indexes.append(plan["Index Name"]) |
| 81 | for v in plan.values(): |
| 82 | indexes.extend(_plan_indexes(v)) |
| 83 | elif isinstance(plan, list): |
| 84 | for item in plan: |
| 85 | indexes.extend(_plan_indexes(item)) |
| 86 | return indexes |
| 87 | |
| 88 | |
| 89 | async def _explain(session: AsyncSession, sql: str, params: StrDict) -> JSONValue: |
| 90 | """Run EXPLAIN (FORMAT JSON, ANALYZE FALSE) and return the parsed plan.""" |
| 91 | explain_sql = f"EXPLAIN (FORMAT JSON, ANALYZE FALSE) {sql}" |
| 92 | result = await session.execute(text(explain_sql), params) |
| 93 | return result.scalar_one() |
| 94 | |
| 95 | |
| 96 | def _has_index_scan(plan: JSONValue, index_name: str) -> bool: |
| 97 | """Return True if the plan uses an Index Scan on ``index_name``.""" |
| 98 | return index_name in _plan_indexes(plan) |
| 99 | |
| 100 | |
| 101 | # ───────────────────────────────────────────────────────────────────────────── |
| 102 | # Tests |
| 103 | # ───────────────────────────────────────────────────────────────────────────── |
| 104 | |
| 105 | |
| 106 | class TestProposalListIndexes: |
| 107 | """EXPLAIN ANALYZE harness — verifies planner chooses the Phase 7 indexes.""" |
| 108 | |
| 109 | @pytest.mark.asyncio |
| 110 | async def test_ix_repo_state_created_used_for_list_sort_newest( |
| 111 | self, db_session: AsyncSession |
| 112 | ) -> None: |
| 113 | """List query with ORDER BY created_at DESC should hit the date index.""" |
| 114 | repo_id = await _make_repo(db_session) |
| 115 | sql = """ |
| 116 | SELECT proposal_id FROM musehub_proposals |
| 117 | WHERE repo_id = :repo_id AND state = 'open' |
| 118 | ORDER BY created_at DESC |
| 119 | LIMIT 50 |
| 120 | """ |
| 121 | plan = await _explain(db_session, sql, {"repo_id": repo_id}) |
| 122 | # Accept either Index Scan or Bitmap Index Scan |
| 123 | assert _has_index_scan(plan, "ix_musehub_proposals_repo_state_created") or \ |
| 124 | _has_index_scan(plan, "ix_musehub_proposals_repo_state"), \ |
| 125 | f"Expected index scan on repo_state_created, got plan:\n{json.dumps(plan, indent=2)[:800]}" |
| 126 | |
| 127 | @pytest.mark.asyncio |
| 128 | async def test_ix_repo_state_risk_used_for_risk_sort( |
| 129 | self, db_session: AsyncSession |
| 130 | ) -> None: |
| 131 | """List query sorted by risk_score should avoid a full sequential scan.""" |
| 132 | repo_id = await _make_repo(db_session) |
| 133 | sql = """ |
| 134 | SELECT proposal_id FROM musehub_proposals |
| 135 | WHERE repo_id = :repo_id AND state = 'open' |
| 136 | ORDER BY risk_score DESC NULLS LAST |
| 137 | LIMIT 50 |
| 138 | """ |
| 139 | plan = await _explain(db_session, sql, {"repo_id": repo_id}) |
| 140 | # The planner may pick the dedicated risk index, the base state index, or |
| 141 | # any other composite proposal index depending on table statistics. |
| 142 | # What matters is that it uses index-based access — not a Seq Scan. |
| 143 | nodes = _plan_nodes(plan) |
| 144 | assert "Index Scan" in nodes or "Bitmap Index Scan" in nodes or \ |
| 145 | "Index Only Scan" in nodes, \ |
| 146 | f"Expected index-based access for risk sort, got plan:\n{json.dumps(plan, indent=2)[:800]}" |
| 147 | |
| 148 | @pytest.mark.asyncio |
| 149 | async def test_ix_review_proposal_state_used_for_approval_prefetch( |
| 150 | self, db_session: AsyncSession |
| 151 | ) -> None: |
| 152 | """Approval prefetch does not full-seq-scan musehub_proposal_reviews.""" |
| 153 | fake_pid = f"sha256:{'a' * 64}" |
| 154 | sql = """ |
| 155 | SELECT proposal_id, COUNT(*) as approved_ct |
| 156 | FROM musehub_proposal_reviews |
| 157 | WHERE proposal_id IN (:pid) AND state = 'approved' |
| 158 | GROUP BY proposal_id |
| 159 | """ |
| 160 | plan = await _explain(db_session, sql, {"pid": fake_pid}) |
| 161 | nodes = _plan_nodes(plan) |
| 162 | # Any index-based access is acceptable — planner may choose the existing |
| 163 | # proposal_id FK index or the new composite index; both avoid a seq scan. |
| 164 | assert "Index Scan" in nodes or "Bitmap Index Scan" in nodes or \ |
| 165 | "Index Only Scan" in nodes, \ |
| 166 | f"Expected index-based access on proposal_reviews, got nodes={nodes}\nplan:\n{json.dumps(plan, indent=2)[:600]}" |
| 167 | |
| 168 | @pytest.mark.asyncio |
| 169 | async def test_ix_identity_handle_type_used_for_author_type_filter( |
| 170 | self, db_session: AsyncSession |
| 171 | ) -> None: |
| 172 | """Author-type filter uses index-based access on musehub_identities.""" |
| 173 | sql = """ |
| 174 | SELECT handle FROM musehub_identities |
| 175 | WHERE handle IN (:h) AND identity_type = 'agent' |
| 176 | """ |
| 177 | plan = await _explain(db_session, sql, {"h": "gabriel"}) |
| 178 | nodes = _plan_nodes(plan) |
| 179 | # The unique handle constraint or the new composite index both work; |
| 180 | # either way the planner should not do a seq scan. |
| 181 | assert "Index Scan" in nodes or "Bitmap Index Scan" in nodes or \ |
| 182 | "Index Only Scan" in nodes, \ |
| 183 | f"Expected index-based access on musehub_identities, got nodes={nodes}\nplan:\n{json.dumps(plan, indent=2)[:600]}" |
| 184 | |
| 185 | @pytest.mark.asyncio |
| 186 | async def test_all_four_indexes_exist_in_pg_catalog( |
| 187 | self, db_session: AsyncSession |
| 188 | ) -> None: |
| 189 | """All 4 Phase 7 indexes must be present in pg_indexes.""" |
| 190 | expected = { |
| 191 | "ix_musehub_proposals_repo_state_created", |
| 192 | "ix_musehub_proposals_repo_state_risk", |
| 193 | "ix_musehub_proposal_reviews_proposal_state", |
| 194 | "ix_musehub_identities_handle_type", |
| 195 | } |
| 196 | result = await db_session.execute( |
| 197 | text( |
| 198 | "SELECT indexname FROM pg_indexes " |
| 199 | "WHERE schemaname = 'public' AND indexname = ANY(:names)" |
| 200 | ), |
| 201 | {"names": list(expected)}, |
| 202 | ) |
| 203 | found = {row[0] for row in result.fetchall()} |
| 204 | missing = expected - found |
| 205 | assert not missing, f"Phase 7 indexes missing from pg_indexes: {missing}" |
File History
14 commits
sha256:34035d72cef530c1ab9d6a6f53be18d803dde705fc3157617d70352a96d0747b
Merge branch 'fix/wire-push-external-parent-manifest' into dev
Human
8 days ago
sha256:fc04e4cae9e1774d6a21b65c45daeed0e6787eb581d13aa1b03bfe9384a34226
Merge branch 'fix/two-column-scroll-layout' into dev
Human
8 days ago
sha256:408916fc5973ba59c6e4eebaa80ebdcc801c0a63205651e25009d11548f79454
chore: bump version to 0.2.0.dev2 — nightly.2, matching muse
Sonnet 4.6
patch
11 days ago
sha256:d035733f21ccff27735fddebfbbe0ed24565a32a22db8de5885402262671ecd2
chore: bump version to 0.2.0rc15 for musehub#113 fix release
Sonnet 4.6
patch
14 days ago
sha256:0032d6cfa33bc3c8367436ad768e7dd0e339b4332153160247da8266cb5fa352
Merge branch 'task/version-tags-phase3-server' into dev
Human
17 days ago
sha256:4669620efda9ff41c55bdefd1f7bfe1c239d468428744c84ead9957e5a003a53
merge: rescue snapshot-recovery hardening (c00aa21d) into d…
Opus 4.8
minor
⚠
29 days ago
sha256:a59da49c4611b970fc4b6ae48678ce4943261c213a07ddbd73ce9201df869b4a
fix: remove false-positive proposal_comments index drop fro…
Sonnet 4.6
patch
33 days ago
sha256:0a240d6dbff234f07d98a28a4a9a68db702f3f9ff9260196f24219bdb1c0b6f3
feat: render markdown mists as HTML with heading anchor links
Sonnet 4.6
patch
34 days ago
sha256:24a7d47486ebc4ebd1832830580e177ec6f877b48dced8c000e198cdec4ce9d6
Merge 'task/bump-version-rc12' into 'dev' — proposal: Bump …
Human
35 days ago
sha256:b9ff931d147e0114a1f17060f415b89ed551c170a91ff226c70437aa5c85f9ee
Merge 'task/bump-version-rc12' into 'dev' — proposal: Bump …
Human
35 days ago
sha256:d1122d21e73471879b460037b22c0b50fded7c423444a176f248428f75dac39c
Merge 'task/fix-issue-pagination-cursor' into 'dev' — propo…
Human
35 days ago
sha256:01e18975e73d2b3cd5b6db7929c895bef9aa6e0d4391dc5b2adfc548b41318dd
Merge 'feat/adding-debug-logs-to-staging' into 'dev' — prop…
Human
35 days ago
sha256:6b1949fc2797ca4c1936a637a4cbfec828ef56cf52398a2e74ca3c4f494e728f
fix: use wire_bytes not mpack_bytes_raw in compute_object_b…
Sonnet 4.6
patch
48 days ago
sha256:b99f2455dc346966d040133f5203297e6e3ef5803a93728a2c30568d0a0f7583
rename: delta_add → delta_upsert across wire format, models…
Sonnet 4.6
patch
50 days ago