POSTGRES QUERY OPTIMIZATION

QUERY SUBMITTED:
SELECT u.name, p.title, count(c.id)
FROM users u
JOIN posts p ON u.id = p.user_id
LEFT JOIN comments c ON p.id = c.post_id
WHERE created_at > now() - interval '30 days'
GROUP BY u.id, p.id;

EXPLAIN ANALYZE OUTPUT (execution time 4200ms, 850MB peak memory)
Seq Scan on posts p (cost 0..1200000 rows=980000)
Seq Scan on comments c (cost 0..850000 rows=2100000) ← BOTTLENECK
Hash Join

DIAGNOSIS:
1. No index on comments.post_id — every group by triggers table scan
2. No index on created_at filter — scans entire posts table even for 30-day window
3. LEFT JOIN pulls all comments regardless of date window — exploding result set

OPTIMIZATION STEPS (in order):

1. ADD INDEXES (5 min, zero downtime with CONCURRENTLY)
CREATE INDEX CONCURRENTLY idx_comments_post_id ON comments(post_id);
CREATE INDEX CONCURRENTLY idx_posts_created_at ON posts(created_at DESC);
CREATE INDEX CONCURRENTLY idx_posts_user_created ON posts(user_id, created_at) INCLUDE (id);

2. RESTRUCTURE QUERY (push date filter down)
SELECT u.name, p.title, count(c.id)
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id AND c.created_at > now() - interval '30 days'
WHERE p.created_at > now() - interval '30 days'
GROUP BY u.id, p.id;
← Date filter on LEFT JOIN prevents full table scan of all comments

3. DENORMALIZE IF QUERY RUNS 1000x/day
Add posts.comment_count_30d → update via trigger on comment insert/delete
Eliminate the LEFT JOIN entirely in read-heavy scenarios

EXPECTED RESULT: 4200ms → 180ms (23x speedup)
New plan: Index Scan on idx_posts_created_at → Nested Loop Join → Index Scan on idx_comments_post_id

VERIFY: Run ANALYZE posts; ANALYZE comments; then re-run EXPLAIN ANALYZE to confirm