Database Query Plan Debugging

Query plan reading is a skill; the patterns are well-known; the time saved per debug session is large.

Why plans matter

Slow query reasons: bad plan; missing index; bad statistics; data distribution surprise.

EXPLAIN ANALYZE reveals which.

Four common plan problems

• Sequential scan when expected indexed.
• Wrong join type (hash vs merge vs nested).
• Bad row estimate (off by 10x+).
• External sort or external hash (memory pressure).

Per-problem fix

Seq scan: index missing or planner thinks table small.

Wrong join: ANALYZE the tables; refresh statistics.

Row estimate: ALTER TABLE ... SET STATISTICS for skewed columns.

External: tune work_mem.

Workflow

1. EXPLAIN ANALYZE the query. 2. Identify the most expensive node. 3. Check estimated vs actual rows. 4. Apply the appropriate fix.

5-10 minutes per query if you know the patterns.

Antipatterns

• Reading plan without understanding cost units. Wrong conclusion.
• Adding indexes blindly. Slow writes.
• Trusting EXPLAIN without ANALYZE. Estimate-only; no real numbers.

What to do this week

Three moves. (1) Apply this pattern to your most-loaded table. (2) Measure query latency / write throughput before/after. (3) Document the win and the constraint so the next refactor inherits the knowledge.