TK-5: fix Hennepin assemblage guard to be prod-scale safe (ANALYZE + PK-driven)

fcf4fb9fbd64b980b7d48c011f3d4b36c165ce71 · 2026-08-11 12:19:46 -0700 · Steve Abrams

The bulk-guard UPDATE hung ~4h on the 15M-row prod parcel table: after bulk-loading
447k rows, stale planner stats made the county_fips filter seq-scan the whole table
(same hazard wake_nc.ts documents). Fix: ANALYZE parcel before the guard so the
(county_fips,source_id) PK index range-scans just the county's rows, and drop the
intermediate MATERIALIZED CTE (removes a second full scan). Verified fast locally;
re-applying to prod Hennepin (parcels+events already loaded).

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

M src/ingest/parcels/hennepin_mn.ts

Diff

commit fcf4fb9fbd64b980b7d48c011f3d4b36c165ce71
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Aug 11 12:19:46 2026 -0700

    TK-5: fix Hennepin assemblage guard to be prod-scale safe (ANALYZE + PK-driven)
    
    The bulk-guard UPDATE hung ~4h on the 15M-row prod parcel table: after bulk-loading
    447k rows, stale planner stats made the county_fips filter seq-scan the whole table
    (same hazard wake_nc.ts documents). Fix: ANALYZE parcel before the guard so the
    (county_fips,source_id) PK index range-scans just the county's rows, and drop the
    intermediate MATERIALIZED CTE (removes a second full scan). Verified fast locally;
    re-applying to prod Hennepin (parcels+events already loaded).
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 src/ingest/parcels/hennepin_mn.ts | 26 +++++++++++++++-----------
 1 file changed, 15 insertions(+), 11 deletions(-)

diff --git a/src/ingest/parcels/hennepin_mn.ts b/src/ingest/parcels/hennepin_mn.ts
index 533c68e..ef82d39 100644
--- a/src/ingest/parcels/hennepin_mn.ts
+++ b/src/ingest/parcels/hennepin_mn.ts
@@ -163,21 +163,25 @@ export async function ingestHennepin(opts: { maxPages?: number } = {}): Promise<
       // in-DB assemblage guard: this feed has no deed book/page, so a portfolio /
       // multi-parcel sale stamps the SAME (SALE_DATE, SALE_VALUE) onto every parcel
       // in the deal (verified: 25 Hennepin PINs share 2021-06 @ $2.3M). Without this
-      // each parcel would record the full price → N-fold inflated sales. Mirror the
-      // Wake guard: any exact (last_sale_date, last_sale_price) shared by >1 parcel
-      // is un-allocatable → null the price + flag bulk (pkey join keeps it indexed).
+      // each parcel would record the full price → N-fold inflated sales. Any exact
+      // (last_sale_date, last_sale_price) shared by >1 parcel is un-allocatable →
+      // null the price + flag bulk.
+      // ANALYZE first: after bulk-loading 447k rows into the multi-million-row prod
+      // parcel table, stale planner stats made the `county_fips=$1` filter SEQ-SCAN
+      // the whole table (a ~4h prod hang, 2026-08-11). Fresh stats let the
+      // (county_fips, source_id) PK index range-scan just this county's rows, and
+      // dropping the intermediate CTE removes a second full scan.
+      await query(`ANALYZE parcel`);
       const b = await query(
-        `WITH grp AS MATERIALIZED (
+        `WITH grp AS (
            SELECT last_sale_date, last_sale_price, COUNT(*)::int AS cnt
              FROM parcel WHERE county_fips=$1 AND last_sale_price IS NOT NULL AND last_sale_date IS NOT NULL
-            GROUP BY 1,2 HAVING COUNT(*) > 1),
-         targets AS MATERIALIZED (
-           SELECT p.source_id, g.cnt FROM parcel p
-             JOIN grp g ON p.last_sale_date=g.last_sale_date AND p.last_sale_price=g.last_sale_price
-            WHERE p.county_fips=$1)
+            GROUP BY 1,2 HAVING COUNT(*) > 1)
          UPDATE parcel p SET last_sale_price = NULL,
-           extra = p.extra || jsonb_build_object('bulk_sale', true, 'bulk_parcel_count', t.cnt)
-           FROM targets t WHERE p.county_fips=$1 AND p.source_id = t.source_id RETURNING 1`, [FIPS]);
+           extra = p.extra || jsonb_build_object('bulk_sale', true, 'bulk_parcel_count', grp.cnt)
+           FROM grp WHERE p.county_fips=$1
+             AND p.last_sale_date = grp.last_sale_date AND p.last_sale_price = grp.last_sale_price
+           RETURNING 1`, [FIPS]);
       bulk = b.rowCount ?? 0;
       const n = await registerParcelSource(FIPS, LAYER,
         `Hennepin County MN (Minneapolis) via MN Met Council Metropolitan 7-County Parcels aggregate (CO_NAME='HENNEPIN' filter) — ${upserted} parcels: owner (100%), situs address + city + zip, EMV land+bldg+TOTAL (estimated MARKET value, ~95%), year built (~94%), units, use class, priced last-sale (SALE_VALUE + SALE_DATE, ~87%) → ${sales} parcel_event sale rows. sqft (FIN_SQ_FT) not populated for Hennepin in this aggregate. No beds/baths or zoning. ${bulk} multi-parcel (assemblage/portfolio) sales flagged bulk + price nulled.`);

← c112f07 TK-5: add 2 metro parcel adapters — Hennepin MN (Minneapolis · back to Nationalrealestate · usre: add commercial deals feed + per-deal detail API over r 41cb360 →