Execution Plans

Introduction

An execution plan is the optimizer's chosen recipe to run your query. EXPLAIN shows it; EXPLAIN ANALYZE runs it and shows actual times.

Understanding the topic

Core concepts to understand:

• Read top-down, left to right; deepest nodes run first.
• Look for: Seq Scan on big tables, Hash vs Index, large rows-out estimate vs actual.
• Big actual >> estimate → stale stats.
• Cost is in arbitrary units; actual is in milliseconds.
• Tools: auto_explain, pgBadger, MySQL slow log.

Syntax reference

Visual workflow / architecture:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 7;

Index Scan using orders_user_id_idx on orders
  (cost=0.43..18.50 rows=10 width=64)
  (actual time=0.040..0.120 rows=12 loops=1)
Planning Time: 0.2 ms
Execution Time: 0.18 ms

Real-world use

Every postmortem of a slow query shows up first as a bad plan: Seq Scan instead of Index, or wildly wrong row estimates.

Best practices

• Always EXPLAIN ANALYZE before shipping perf-critical SQL.
• Compare estimated vs actual rows — gap → stale stats.
• Save plans before/after to prove improvements.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. EXPLAIN vs EXPLAIN ANALYZE.
• Q2. Seq Scan vs Index Scan.
• Q3. Row estimate way off — why?
• Q4. Hash join nodes — what to look for.
• Q5. Tools to find slow queries.