`scripts/migrate_010_search_trgm.sql`

42 lines
-- migrate_010_search_trgm.sql — trigram GIN indexes for the public text search.
--
-- WHY: /api/search and /api/places filter with a LEADING-wildcard LIKE '%q%' on
-- lower(name)/lower(address)/lower(description). A leading wildcard can't use a btree,
-- so every search full-scanned the places table (34k rows and growing). The
-- /api/places + /api/search COUNT(*) queries have no LIMIT, so they ALWAYS scanned
-- the whole table; the list queries scanned too (and /api/search can't even
-- short-circuit on a PK-ordered LIMIT because it ORDER BYs a computed rank).
--
-- With pg_trgm these `LOWER(col) LIKE '%q%'` predicates become Bitmap Index Scans.
-- Verified on the dev DB: EXPLAIN went from a seq/index-filter scan of all rows to
--   Bitmap Heap Scan -> BitmapOr(idx_places_name_trgm, _addr_trgm, _desc_trgm).
-- The index expression `lower(col) gin_trgm_ops` matches the query's `LOWER(col)`
-- exactly (an index on `col` alone would NOT be used for the case-folded predicate).
--
-- CORRECTNESS: indexes change only the PLAN, never the result set — search output is
-- unchanged. NULL address/description are handled by GIN (a NULL simply isn't indexed
-- and LIKE on NULL is not-true, same as before).
--
-- WRITE COST (benchmarked, immaterial): a GIN trgm write adds ~44µs/row vs a plain
-- update (measured: a full 34k-row UPDATE of all 3 columns = 4.31s vs 2.83s). The
-- ingest scrapers that touch name/address/description (upsertPlace) write ONE row per
-- HTTP fetch, sleep-throttled, network-bound at 100s of ms/row — the ~44µs GIN cost is
-- lost in the noise. hacienda-enricher only writes hacienda_* columns, so it never
-- touches these indexes at all (HOT-eligible updates). So the write overhead is real
-- but negligible for this codebase's actual write pattern.
--
-- CONCURRENTLY: built with CREATE INDEX CONCURRENTLY so the prod build does NOT take a
-- SHARE lock that blocks writes to the live `places` table while the scrapers run.
-- apply-migrations.sh runs this file OUTSIDE a transaction (only 004/008/009 are wrapped),
-- so CONCURRENTLY is safe through the existing runner. If a CONCURRENTLY build is
-- interrupted it leaves an INVALID index — recover with:
--   DROP INDEX CONCURRENTLY IF EXISTS idx_places_<col>_trgm;  -- then re-run this file.
--
-- PROD-APPLY (Steve-gated): CREATE EXTENSION pg_trgm requires SUPERUSER / rds_superuser.
-- On Kamatera prod, run the CREATE EXTENSION as the superuser first, then this file.
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_places_name_trgm ON places USING gin (lower(name)        gin_trgm_ops);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_places_addr_trgm ON places USING gin (lower(address)     gin_trgm_ops);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_places_desc_trgm ON places USING gin (lower(description) gin_trgm_ops);