asseeninmovies: /movies perf — 3 partial indexes (has_poster / popularity_w_poster / has_headshot) + drop the (popularity IS NOT NULL) expression prefix that blocked index-order traversal. /movies?sort=popular cold 3.9s → 494ms warm 110ms. Plus migrations/2026-05-12_perf_indexes.sql for reproducibility.

b7e1d47a6c923b37834a0ddcae8198eed25e18ef · 2026-05-12 17:41:30 -0700 · SteveStudio2

Files touched

A migrations/2026-05-12_perf_indexes.sql
M server.js

Diff

commit b7e1d47a6c923b37834a0ddcae8198eed25e18ef
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Tue May 12 17:41:30 2026 -0700

    asseeninmovies: /movies perf — 3 partial indexes (has_poster / popularity_w_poster / has_headshot) + drop the (popularity IS NOT NULL) expression prefix that blocked index-order traversal. /movies?sort=popular cold 3.9s → 494ms warm 110ms. Plus migrations/2026-05-12_perf_indexes.sql for reproducibility.
---
 migrations/2026-05-12_perf_indexes.sql | 25 +++++++++++++++++++++++++
 server.js                              |  6 +++++-
 2 files changed, 30 insertions(+), 1 deletion(-)

diff --git a/migrations/2026-05-12_perf_indexes.sql b/migrations/2026-05-12_perf_indexes.sql
new file mode 100644
index 0000000..5bb9136
--- /dev/null
+++ b/migrations/2026-05-12_perf_indexes.sql
@@ -0,0 +1,25 @@
+-- 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;
diff --git a/server.js b/server.js
index fbef5e5..49b351f 100644
--- a/server.js
+++ b/server.js
@@ -1024,7 +1024,11 @@ app.get('/person/:id', async (req, res) => {
 app.get('/movies', async (req, res) => {
   try {
     const MOVIE_SORTS = {
-      popular:  `(popularity IS NOT NULL) DESC, popularity DESC NULLS LAST, release_year DESC NULLS LAST, id DESC`,
+      // `popularity DESC NULLS LAST, id DESC` matches
+      // idx_asim_movies_popularity_w_poster — the prior
+      // `(popularity IS NOT NULL) DESC` prefix was an expression that broke
+      // index-order use and forced a 290k-row sort.
+      popular:  `popularity DESC NULLS LAST, id DESC`,
       newest:   `release_year DESC NULLS LAST, id DESC`,
       oldest:   `release_year ASC NULLS LAST, id`,
       title_az: `title ASC, id`,

← dbd4763 asseeninmovies: /movies + /people — parallelize page query a · back to AsSeenInMovies · asseeninmovies: GET /api/spotted/stats — JSON counts (pendin ae66c63 →