The planner chooses join order and index usage from statistics. EXPLAIN shows the plan; EXPLAIN ANALYZE executes and reports actual timings—essential tuning workflow.
Reading a plan
EXPLAIN ANALYZE
SELECT o.*
FROM orders o
WHERE o.customer_id = 42
ORDER BY o.created_at DESC
LIMIT 20;Practice: Run on a local Postgres instance you own. Avoid changing production cluster settings.
Key nodes
- Seq Scan — reads whole table—often bad on large tables
- Index Scan / Index Only Scan — uses index; only scan is better
- Nested Loop / Hash Join — join strategies with different cost profiles
- Buffers — shared hit vs read shows cache effectiveness
Tuning loop
Add or adjust indexes, UPDATE statistics, rewrite SQL (CTE vs join), re-run EXPLAIN ANALYZE. Compare with SQL track EXPLAIN intro concepts.
Important interview questions and answers
- Q: EXPLAIN ANALYZE side effect?
A: Actually runs the query—careful with DELETE/UPDATE without safeguards. - Q: Seq Scan always bad?
A: Sometimes cheaper on tiny tables—context matters.
Self-check
- What does EXPLAIN ANALYZE add over EXPLAIN?
- When might Seq Scan be acceptable?
Pitfall: EXPLAIN ANALYZE runs the query—avoid destructive SQL without safeguards.
Interview prep
- EXPLAIN ANALYZE?
Executes query and shows actual timings.
- Seq Scan?
Full table read—often costly on large tables.