How do you read an EXPLAIN plan at a high level?

Deep dive

`EXPLAIN` shows the query plan: the steps the database *intends* to take. `EXPLAIN ANALYZE` runs the query and shows *actual* timing/rows.

How to read it (high level)

1) Focus on the nodes with the biggest time/cost. 2) Identify access paths:

• sequential scan vs index scan
• where filtering happens

3) Compare row estimates vs actual rows:

• big mismatches often mean stale statistics or skewed data.

4) Understand join strategy (names vary by DB):

• nested loop (good for small inputs / indexed lookups)
• hash join (good for large, needs memory)
• merge join (good for sorted inputs)

5) Watch for expensive sorts, temp spills, and repeated loops.

Typical fixes

• Add/adjust indexes for filters and join keys.
• Rewrite query to reduce rows earlier (filter before join, avoid `SELECT *`).
• Refresh stats (e.g., `ANALYZE`) if estimates are wrong.

Common pitfalls

• Confusing planner “cost” with actual runtime.
• Forcing indexes/hints without understanding selectivity.
• Ignoring that a slow node inside a loop can be multiplied many times.