wake: optimize bulk-flag UPDATE to pkey join (was 53min JSONB-expr seq-scan on 5M-row prod table)

d32c8702c421b9c142efe4667d1c4f7ac6366bb1 · 2026-07-26 15:40:57 -0700 · Steve Abrams

The bulk-flag UPDATE joined on extra->>'latest_deed' (JSONB, unindexable) — fine locally, but seq-scanned for ~53min on the remote 5M-row parcel table. Now: materialized CTE resolves bulk parcels to source_id, UPDATE joins on the PKEY (county_fips,source_id). Same pattern in miami/franklin adapters is a latent perf risk (deployed OK when table was smaller). TK-00026.

Files touched

M src/ingest/parcels/wake_nc.ts

Diff

commit d32c8702c421b9c142efe4667d1c4f7ac6366bb1
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 26 15:40:57 2026 -0700

    wake: optimize bulk-flag UPDATE to pkey join (was 53min JSONB-expr seq-scan on 5M-row prod table)
    
    The bulk-flag UPDATE joined on extra->>'latest_deed' (JSONB, unindexable) — fine locally, but seq-scanned for ~53min on the remote 5M-row parcel table. Now: materialized CTE resolves bulk parcels to source_id, UPDATE joins on the PKEY (county_fips,source_id). Same pattern in miami/franklin adapters is a latent perf risk (deployed OK when table was smaller). TK-00026.
---
 src/ingest/parcels/wake_nc.ts | 17 ++++++++++++-----
 1 file changed, 12 insertions(+), 5 deletions(-)

diff --git a/src/ingest/parcels/wake_nc.ts b/src/ingest/parcels/wake_nc.ts
index efbbdfd..18c194c 100644
--- a/src/ingest/parcels/wake_nc.ts
+++ b/src/ingest/parcels/wake_nc.ts
@@ -125,15 +125,22 @@ export async function ingestWake(): Promise<{ upserted: number }> {
     }
     if (upserted < 380000) throw new Error(`only ${upserted} Wake parcels (expected ~437k) — layer/paging drift?`);
 
-    // in-DB bulk flag: a deed spanning >1 REID is an assemblage; null the un-allocatable price + flag.
+    // in-DB bulk flag: a deed spanning >1 REID is an assemblage; null the un-allocatable
+    // price + flag. Resolve bulk parcels to their source_id in a materialized CTE, then
+    // UPDATE joining on the PKEY (county_fips, source_id) — NOT on the JSONB expression
+    // extra->>'latest_deed', which can't use an index and seq-scanned for ~53min on the
+    // 5M-row prod parcel table. The pkey join makes the write index-driven.
     const b = await query(
-      `WITH bulk AS (
+      `WITH deeds AS MATERIALIZED (
          SELECT extra->>'latest_deed' AS deed, COUNT(*)::int AS cnt
            FROM parcel WHERE county_fips=$1 AND extra->>'latest_deed' IS NOT NULL AND extra->>'latest_deed' !~ '^[-0]*$'
-          GROUP BY 1 HAVING COUNT(*) > 1)
+          GROUP BY 1 HAVING COUNT(*) > 1),
+       targets AS MATERIALIZED (
+         SELECT p.source_id, d.cnt FROM parcel p JOIN deeds d ON p.extra->>'latest_deed' = d.deed
+          WHERE p.county_fips=$1)
        UPDATE parcel p SET last_sale_price = NULL,
-         extra = p.extra || jsonb_build_object('bulk_sale', true, 'bulk_deed_folio_count', bulk.cnt)
-         FROM bulk WHERE p.county_fips=$1 AND p.extra->>'latest_deed' = bulk.deed RETURNING 1`, [FIPS]);
+         extra = p.extra || jsonb_build_object('bulk_sale', true, 'bulk_deed_folio_count', t.cnt)
+         FROM targets t WHERE p.county_fips=$1 AND p.source_id = t.source_id RETURNING 1`, [FIPS]);
     const bulk = b.rowCount ?? 0;
 
     const n = await registerParcelSource(FIPS, LAYER,

← 9a14bfd Scout round 2: add Crook OR (w/ names), Sonoma CA, Colorado · back to Nationalrealestate · USRE Markets: left-rail filters now cover ALL data points (d 4d66223 →