# Optimizing Database Queries from 2s to 50ms

Real performance debugging session. Walk through EXPLAIN ANALYZE results, index strategies, and query rewriting.


**Model:** claude-opus-4
**Created:** 7/30/2026

---

## Conversation

### **Human**

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;

---

### **Assistant**

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

---

### **Human**

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

---

### **Assistant**

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).

---


---

*Downloaded from BranchLM - GitHub for AI Sessions*