`db/migrations/024_perf_indexes.sql`

41 lines
-- Performance indexes flagged by perf-engineer in 2026-05-05 review.
-- All idempotent — IF NOT EXISTS guards. CONCURRENTLY would be ideal at
-- LA scale but isn't allowed inside a transaction; psql -f wraps each
-- file in a single tx, so we use plain CREATE INDEX. Acceptable since
-- pastdoor's listing/place_event tables are <1M rows.

-- listing.source — partial index for the public-listings filter on
-- FlagshipHome.getFeaturedHouses (`source = 'wikipedia_house'`) and
-- houses/page (`source IN ('wikipedia_house','la_hcm')`).
CREATE INDEX IF NOT EXISTS idx_listing_source
  ON listing (source) WHERE is_public = true;

-- listing.ingested_at DESC — used as ORDER BY in searchListings,
-- getNearbyListings, /api/search. With LIMIT N a btree-DESC index
-- means limit-via-index instead of full sort.
CREATE INDEX IF NOT EXISTS idx_listing_ingested_at
  ON listing (ingested_at DESC) WHERE is_public = true;

-- listing.claimed_at DESC — ClaimHome.getRecentClaims orders by
-- claimed_at. Existing idx_listing_claimed is on claimed_by.
CREATE INDEX IF NOT EXISTS idx_listing_claimed_at
  ON listing (claimed_at DESC) WHERE claimed_by IS NOT NULL;

-- place_event composite (kind, valid_from DESC) — getRecentPermits
-- filters on kind+public_visible+ORDER BY valid_from DESC. Today the
-- planner picks one of the two single-column indexes; the composite
-- supports both.
CREATE INDEX IF NOT EXISTS idx_place_event_kind_validfrom
  ON place_event (kind, valid_from DESC) WHERE public_visible;

-- pg_trgm on listing.title and entity.display_name for ILIKE %x%
-- supplied by /api/search and houses/page. Existing idx_listing_search_doc
-- is tsvector-only; bare ILIKE doesn't use it.
CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX IF NOT EXISTS idx_listing_title_trgm
  ON listing USING gin (title gin_trgm_ops);

CREATE INDEX IF NOT EXISTS idx_entity_name_trgm
  ON entity USING gin (display_name gin_trgm_ops);