Optimizing Database Queries from 2s to 50ms

My PostgreSQL query takes 2 seconds on a table with 10M rows. Here's the query: SELECT u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.created_at > 2024-01-01 GROUP BY u.name HAVING COUNT(o.id) > 10;

Let me analyze this systematically. First, let's see what execution plan you're getting. Can you share the EXPLAIN ANALYZE output? I need to see whether it's using indexes, where the time is being spent, and join strategy. While you get that, I spot THREE likely issues: 1. No index on orders.created_at - this forces a sequential scan 2. LEFT JOIN plus WHERE on the right table - this acts like an INNER JOIN but slower 3. No index on orders.user_id - the join is probably doing nested loops

Here's the EXPLAIN ANALYZE output: Seq Scan on orders o taking 890ms, with a Sort operation that takes additional time.

Perfect! I can see exactly what's wrong: The bottleneck: Sequential Scan on orders taking 890ms PostgreSQL is doing a sequential scan on 10M rows, filtering by date, THEN sorting by user_id for the merge join. The fix: Create an index that covers both the filter and the join: CREATE INDEX idx_orders_user_created ON orders(user_id, created_at); This compound index lets PostgreSQL use the index for the date filter and the same index for the join (already sorted by user_id). It avoids the sort entirely. Expected speed: 2 seconds to about 50ms (40x faster).