builtbykev
7efb04e280
TWO DEFECTS, one lookup. SCALE. The family lookup sent 100 natural keys as a PostgREST IN-list. `read_natural_key` has NO pg_stats row at all -- the table's last autoanalyze (2026-08-26) predates the column ever being populated -- so the planner used a default per-value selectivity, estimated 172,409 rows and chose a sequential scan of 344,818: 8.5s, then 57014. At 50 keys the same shape returned in ~357ms. The cliff is a statistics artifact, not a volume one, which is why the repair does not depend on the estimate improving and is not CH=50. `readNaturalKey` builds `sport|game_date|player_key|stat|side|line[|#event]`, so SPORT AND GAME_DATE ARE COMPONENTS OF THE KEY. Two rows sharing a key necessarily share both, and scoping the lookup to the (sport, game_date) pairs present in the requested keys is LOSSLESS BY CONSTRUCTION. One index-backed range per date, walked with safePaginate; cost is bounded by ONE SLATE however long the chronology gets. Measured: 5,000 keys -> 1 scope, and the plan is `Index Scan using model_snapshots_lineage_family_idx, cost 0.28..1.92`. VALIDITY. A row carrying `read_natural_key` is not history: the key is stamped on every candidate BEFORE the lookup, so a failure leaves it on a row that never became an action. Proven this was not cosmetic -- fed the raw rows the old lookup returned, the resolver produced a REVISION with a NULL read_id (an orphaned chain node) and labelled a brand-new Read LEGACY_UNVERIFIED. `isValidLineageAction` states what a completed action IS: all nine fields, in the query and again in code. ATOMICITY. A failed attempt now leaves NO lineage-specific state. `publication_id`/`published_at` are untouched -- the slate really was published, and erasing a true fact to tidy a false one is the wrong repair. Replayed the exact failed 19:00Z cohort through the real resolver, side-effect free: 119 NEW / 379 CHANGED / 621 UNCHANGED -> ORIGIN 119 / REVISION 379 / RECAPTURE 621, 0 wrong parent, 0 wrong ordinal, 0 null read_id, 0 forks -- byte-identical with all 1,119 failed partial rows present. Clean-head parity 1,024/1,024. Migration 050 is CONCURRENTLY + IF NOT EXISTS, drops nothing, rewrites nothing. Lineage stays OFF. Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01CQJeAG8vcDoL5zkiaJyVb8
28 lines
1.5 KiB
SQL
28 lines
1.5 KiB
SQL
-- 048b — supporting index for the lineage family lookup.
|
|
--
|
|
-- WHY. The family lookup was `read_natural_key IN (<100 keys>)`. Measured
|
|
-- 2026-08-28: `read_natural_key` has NO pg_stats row at all (the table's last
|
|
-- autoanalyze predates the column ever being populated), so the planner used a
|
|
-- default per-value selectivity, estimated 172,409 rows for 100 keys, and chose
|
|
-- a sequential scan of 344,818 rows -- 8.5s, then 57014. At 50 keys the same
|
|
-- shape planned differently and returned in ~357ms. That cliff is a statistics
|
|
-- artifact, not a data-volume one, which is why the repair does not depend on
|
|
-- the estimate improving.
|
|
--
|
|
-- The lookup is now scoped by (sport, game_date) -- both are COMPONENTS OF THE
|
|
-- NATURAL KEY ITSELF, so the bound is lossless by construction -- and reads only
|
|
-- COMPLETED lineage actions. This index serves exactly that shape:
|
|
--
|
|
-- Index Scan using model_snapshots_lineage_family_idx (cost=0.28..1.92)
|
|
--
|
|
-- Its size grows with completed lineage actions, and the query's date bound
|
|
-- means a lookup scans ONE slate's worth however long the chronology gets.
|
|
--
|
|
-- FORWARD-ONLY and NON-DESTRUCTIVE: CONCURRENTLY (no table lock, no rewrite),
|
|
-- IF NOT EXISTS (safe to re-run), and no existing index is dropped.
|
|
-- Built on production 2026-08-28: 128 kB over 1,024 covered rows, indisvalid.
|
|
|
|
create index concurrently if not exists model_snapshots_lineage_family_idx
|
|
on public.model_snapshots (game_date, sport, read_natural_key)
|
|
where lineage_action is not null;
|