perf: replace idx_tx_last_seen with partial index WHERE last_seen=0

[Kpa-clawbot]

Context

Raised by Carmack in PR #1735 (round-1 review). Currently idx_tx_last_seen indexes (last_seen, id) for ALL transmissions; the only consumer (chunkedTxLastSeenBackfill) only ever scans WHERE last_seen=0.

Proposal

Replace with a partial index:

CREATE INDEX idx_tx_last_seen_zero ON transmissions(id) WHERE last_seen=0;

Why

• Index size shrinks to ~the count of unprocessed rows (typically 0 after backfill completes; bounded by ingest rate during normal ops).
• Once the backfill finishes, the partial index becomes near-empty and stops competing for cache.

Risk

• Requires CREATE INDEX migration via RunAsyncMigration (the new index) + a follow-up DROP INDEX idx_tx_last_seen once the partial is built and verified. Two-step.

[Kpa-clawbot]

Severity: P3 — perf hygiene; matters at operator-scale DB cache pressure.

Claim: confirmed (forward-looking). Today idx_tx_last_seen does not yet exist in code (grep on master returns no CREATE) — it's introduced by PR #1735 alongside the backfill. The only consumer is WHERE last_seen=0, so a partial index is strictly tighter.

Findings:

• (carmack): Full (last_seen, id) index on 71K+ rows is wasted bytes after backfill converges to ~0 unprocessed rows. Partial index degenerates to near-empty in steady state, stays out of the page cache. Win is cumulative across cold loads.
• (dijkstra): Two-step migration (CREATE partial → verify → DROP full) is the correct order; never drop first. Both via RunAsyncMigration so neither blocks startup.

Fix path: in dbschema/ add two ordered migrations: (a) CREATE INDEX idx_tx_last_seen_zero ON transmissions(id) WHERE last_seen=0; (b) gated drop of idx_tx_last_seen once (a) is done. Update backfill query plan check (EXPLAIN QUERY PLAN) in tests to assert use of partial. Effort: M.

Dup check: searched "partial index last_seen transmissions" — none open. Adjacent: #1738.

Blocker: depends on PR #1735 creating idx_tx_last_seen first; would be a no-op rename otherwise.

Triage verdict: ready-to-fix