`migrations/2026-05-12_perf_indexes.sql`

26 lines
-- 2026-05-12 perf indexes — speeds /movies, /people, home count(*) queries.
--
-- The /movies?sort=popular page was scanning ~290k asim_movies rows on every
-- cold hit (3.8s exec, 1.1s planning). The fix is twofold:
--   1) Partial covering indexes that match the WHERE poster_url IS NOT NULL
--      and headshot_url IS NOT NULL filters used on the listing pages.
--   2) A popularity-ordered partial index whose key order matches the
--      MOVIE_SORTS.popular ORDER BY clause in server.js, so the planner
--      picks an index-order traversal instead of a top-N sort over the
--      whole 290k-row table.
--
-- All three are CONCURRENTLY-built, so re-running is safe and won't block
-- live readers.

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_asim_movies_has_poster
  ON asim_movies (id) WHERE poster_url IS NOT NULL;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_asim_movies_popularity_w_poster
  ON asim_movies (popularity DESC NULLS LAST, id DESC) WHERE poster_url IS NOT NULL;

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_asim_people_has_headshot
  ON asim_people (id) WHERE headshot_url IS NOT NULL;

ANALYZE asim_movies;
ANALYZE asim_people;