9790: document load-bearing dw_sku_registry functional index (replay on DB rebuild)

6e38f8b634a1386247b731206b4b54353b51fd3b · 2026-06-11 10:39:53 -0700 · SteveStudio2

Files touched

A data/REQUIRED-INDEXES.sql

Diff

commit 6e38f8b634a1386247b731206b4b54353b51fd3b
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Thu Jun 11 10:39:53 2026 -0700

    9790: document load-bearing dw_sku_registry functional index (replay on DB rebuild)
---
 data/REQUIRED-INDEXES.sql | 13 +++++++++++++
 1 file changed, 13 insertions(+)

diff --git a/data/REQUIRED-INDEXES.sql b/data/REQUIRED-INDEXES.sql
new file mode 100644
index 0000000..bb88189
--- /dev/null
+++ b/data/REQUIRED-INDEXES.sql
@@ -0,0 +1,13 @@
+-- Load-bearing indexes for new-import-viewer (:9790) /api/stats.
+-- Replay on any dw_unified rebuild. Without the registry functional index the
+-- netnew NOT EXISTS seq-scans 84k rows per outer row → stats query 75s+/never
+-- → server death spiral (see 2026-06-11 meltdown; commit 0d81ad3).
+
+-- dw_sku_registry: functional index matching NETNEW_PRED's upper(trim(mfr_sku)).
+-- shopify_products already has idx_sp_mfr_upper_trim (its twin).
+CREATE INDEX IF NOT EXISTS idx_registry_mfr_upper_trim
+  ON dw_sku_registry (upper(trim(mfr_sku)));
+ANALYZE dw_sku_registry;
+
+-- NOTE: build PLAIN, not CONCURRENTLY — a CONCURRENTLY build that doesn't finish
+-- validation leaves an INVALID index the planner silently ignores (indisvalid=f).

← 0d81ad3 9790: fix /api/stats death-spiral — statement_timeout guard · back to New Import Viewer · snapshot: 9790 stats fix + required-indexes note 5a2715d →