DISTINCT ON: JOIN vs LEFT JOIN plan differences

distinct-on-join-vs-left-join.sql

-- DISTINCT ON: INNER JOIN vs LEFT JOIN
--
-- With LEFT JOIN, Postgres switches to a Hash Right Join that loses
-- the index's sort order. It hash-joins all 5 million rows, sorts
-- them on disk, and then deduplicates. LIMIT can't stop early
-- because the Unique node sits on top of the sort.
--
-- With INNER JOIN, Postgres can use a Merge Join that walks the covering
-- index in order. The output is already sorted, so LIMIT stops
-- after the first 15 unique users — no disk sort at all.
--
-- INNER JOIN is safe when the data model guarantees every user has at
-- least one status row. LEFT JOIN is the safer default when that
-- invariant isn't enforced.

-- LEFT JOIN, LIMIT 15: ~1,903 ms
-- Hash-joins all rows, sorts on disk, then returns 15.

EXPLAIN ANALYZE
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;

-- Limit  (rows=15)
--   -> Unique  (rows=15)
--        -> Sort  (rows=5000000)
--              Sort Method: external merge  Disk: 330696kB
--              -> Hash Right Join  (rows=5000000)
--                    -> Seq Scan on user_statuses  (rows=5000000)
--                    -> Hash
--                          -> Seq Scan on users  (rows=100000)

-- INNER JOIN, LIMIT 15: ~0.21 ms
-- Walks the covering index in merge-join order, stops at 15.

EXPLAIN ANALYZE
SELECT DISTINCT ON (user_statuses.user_id)
  users.id, users.name, user_statuses.status
FROM users
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;

-- Limit  (rows=15)
--   -> Unique  (rows=15)
--        -> Merge Join  (rows=681)
--              -> Index Only Scan using idx_user_statuses_user_id_created_at
--                    on user_statuses  (rows=681)
--              -> Index Scan using users_pkey on users  (rows=15)

-- LEFT JOIN, all 100,000 users: ~2,823 ms
-- Same hash join + disk sort, but reads all 5M sorted rows.

EXPLAIN ANALYZE
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;

-- Unique  (rows=100000)
--   -> Sort  (rows=5000000)
--         Sort Method: external merge  Disk: 330696kB
--         -> Hash Right Join  (rows=5000000)
--               -> Seq Scan on user_statuses  (rows=5000000)
--               -> Hash
--                     -> Seq Scan on users  (rows=100000)

-- INNER JOIN, all 100,000 users: ~617 ms
-- Merge join walks the index in order, no disk sort needed.

EXPLAIN ANALYZE
SELECT DISTINCT ON (user_statuses.user_id)
  users.id, users.name, user_statuses.status
FROM users
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;

-- Unique  (rows=100000)
--   -> Merge Join  (rows=5000000)
--         -> Index Only Scan using idx_user_statuses_user_id_created_at
--               on user_statuses  (rows=5000000)
--         -> Index Scan using users_pkey on users  (rows=100000)