DISTINCT ON + LIMIT workaround for paginated queries

distinct-on-workaround.sql

-- DISTINCT ON with LIMIT is slow because Postgres can't push the LIMIT
-- through the Unique node. It hash-joins all status rows, sorts them on
-- disk, and only then returns 15.
--
-- Workaround: pre-filter users with a subquery so Postgres only sorts the
-- status rows belonging to those users.

-- Slow (~1,900 ms with 5M status rows):
SELECT DISTINCT ON (user_statuses.user_id)
  users.id, users.name, user_statuses.status
FROM users
LEFT JOIN user_statuses ON user_statuses.user_id = users.id
ORDER BY user_statuses.user_id,
         user_statuses.created_at DESC,
         user_statuses.id DESC
LIMIT 15;

-- Fast (~1.3 ms):
SELECT DISTINCT ON (user_statuses.user_id)
  users.id, users.name, user_statuses.status
FROM users
LEFT JOIN user_statuses ON user_statuses.user_id = users.id
WHERE users.id IN (SELECT id FROM users ORDER BY id LIMIT 15)
ORDER BY user_statuses.user_id,
         user_statuses.created_at DESC,
         user_statuses.id DESC;