EXPLAIN Deep Dive: Understanding PostgreSQL Query Plans

calendar_today May 24, 2025 schedule 6 min read

Learning Objectives: By the end of this lesson, you will be able to:

Interpret the output of PostgreSQL’s EXPLAIN and EXPLAIN ANALYZE commands

Identify and understand key plan nodes (Seq Scan, Index Scan, etc.)

Distinguish between estimated and actual rows/loops in query plans

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever run a slow query and wondered why it was slow? Or been surprised by how a database chose to fetch your data? Today, you’ll learn how to see inside PostgreSQL’s decision-making process!

Lesson Overview

Duration: 90 minutes
Prerequisites: Basic SQL knowledge, ability to run queries in PostgreSQL
Materials Needed: PostgreSQL database (local or cloud), SQL client (psql, DBeaver, DataGrip, etc.)

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

What is the query planner and why does it matter?
How to use EXPLAIN and EXPLAIN ANALYZE
Understanding key plan nodes (Seq Scan, Index Scan, etc.)
Hands-on practice with real queries
Common mistakes and how to avoid them

Success Criteria:

By the end of this session, you should be able to read and interpret a basic PostgreSQL query plan, and use this knowledge to start optimizing your queries.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What is a Query Plan?

A query plan is PostgreSQL’s step-by-step recipe for how it will execute your SQL query. The planner considers multiple strategies and picks the one it thinks will be fastest.

Real-World Analogy: Imagine planning a road trip. You could take the highway (fast, but maybe longer distance), or back roads (slower, but shorter). The planner weighs options and picks a route.

Why This Matters: Understanding query plans helps you:

Diagnose slow queries
See if indexes are being used
Spot inefficiencies (like full table scans)

`EXPLAIN` vs. `EXPLAIN ANALYZE`

EXPLAIN shows the planner’s estimated plan before running the query.
EXPLAIN ANALYZE actually runs the query and shows real execution details (timing, row counts).

Key Plan Nodes

Seq Scan: Sequential scan of the whole table (can be slow for large tables)
Index Scan: Uses an index to find rows (usually faster)
Index Only Scan: Uses only the index (no table lookup needed)
Nested Loop: Joins two tables by looping over one and searching the other
Hash Join: Builds a hash table for joining (good for large, unsorted data)
Merge Join: Joins sorted data efficiently
Sort: Sorts rows for ORDER BY, DISTINCT, etc.
Aggregate: Performs aggregation (e.g., SUM, COUNT)

Key Terminology

Plan Node: A step in the query plan (e.g., Seq Scan, Index Scan)
Estimated Rows: The planner’s guess at how many rows a step will process
Actual Rows: The real number of rows processed (from EXPLAIN ANALYZE)
Loops: How many times a node is executed (important for nested operations)

4. Detailed Explanation and Examples (25-30 minutes)

Understanding Query Plans

A query plan is a tree of nodes. Each node represents an operation (scan, join, sort, etc.). The plan is read from the bottom up: leaves are data sources, and each parent node processes the output of its children.

Practical Applications

Tuning queries for performance
Verifying that indexes are used
Understanding why a query is slow

Common Misconceptions

Misconception: “If I have an index, PostgreSQL will always use it.” Reality: The planner may choose a sequential scan if it thinks it’s faster (e.g., for small tables or broad queries).
Misconception: “Estimated rows are always accurate.” Reality: Estimates can be off if statistics are outdated or data is skewed.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Basic Implementation

EXPLAIN SELECT * FROM users WHERE id = 42;

Explanation:

This shows the planner’s estimated plan for finding a user by ID.
If there’s an index on id, you should see an Index Scan.

Example 2: Practical Application

EXPLAIN ANALYZE SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

Key Points:

EXPLAIN ANALYZE runs the query and shows actual timing and row counts.
If there’s no index on created_at, you may see a Seq Scan.
Adding an index can change the plan to Index Scan and speed up the query.

Example 3: Advanced Technique

EXPLAIN SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

This plan will show join nodes (e.g., Nested Loop, Hash Join).
The planner chooses the join type based on table sizes and indexes.

Debugging Demonstration

Show a common mistake:

-- Common mistake: function on indexed column disables index
EXPLAIN SELECT * FROM users WHERE lower(email) = 'test@example.com';

-- Corrected version (index on lower(email))
CREATE INDEX idx_users_lower_email ON users (lower(email));
EXPLAIN SELECT * FROM users WHERE lower(email) = 'test@example.com';

6. Hands-On Practice (30-40 minutes)

Exercise 1: Basic Practice (10 minutes)

Objective: Run EXPLAIN and EXPLAIN ANALYZE on your own queries.

Instructions:

Pick 2-3 queries you use often.
Run EXPLAIN and then EXPLAIN ANALYZE for each.
Note the plan nodes and estimated vs. actual rows.

Starter Code:

EXPLAIN SELECT * FROM your_table WHERE some_column = 'value';
EXPLAIN ANALYZE SELECT * FROM your_table WHERE some_column = 'value';

Solution:

-- Example output (your plan will differ)
Seq Scan on your_table  (cost=0.00..35.50 rows=5 width=32)
  Filter: (some_column = 'value')

Exercise 2: Intermediate Challenge (15 minutes)

Objective: Compare plans for queries with and without indexes.

Requirements:

Create a table and insert sample data
Run a query without an index and observe the plan
Add an index and rerun the query
Compare the plans

Hints:

Use CREATE INDEX to add an index
Look for Seq Scan vs. Index Scan in the plan

Exercise 3: Creative Application (15 minutes)

Objective: Explore how different queries produce different plans.

Challenge:

Write queries with JOIN, ORDER BY, and GROUP BY
Use EXPLAIN ANALYZE to see how the plan changes

Extension Ideas:

Try queries with subqueries or CTEs
Experiment with different filter conditions

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

Problem: Ignoring the difference between estimated and actual rows Cause: Only using EXPLAIN (not ANALYZE) Solution: Always use EXPLAIN ANALYZE for real performance analysis
Problem: Assuming indexes are always used Cause: Planner may choose a sequential scan Solution: Check the plan and consider query or index changes

Debugging Tips

Use EXPLAIN ANALYZE for real execution details
Update statistics with ANALYZE if estimates seem off
Read plans from the bottom up
Use graphical tools (e.g., explain.depesz.com) for complex plans

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

Always check query plans for slow queries
Keep statistics up to date
Use indexes wisely (not too many, not too few)
Document query changes and their impact

Next Steps in Your Learning Journey

Learn about cost numbers in plans (see Day 2)
Explore advanced plan nodes (joins, aggregates)
Try visualizing plans with online tools

9. Summary and Review (5 minutes)

Key Takeaways

EXPLAIN and EXPLAIN ANALYZE are essential for query tuning
Understanding plan nodes helps you optimize queries
Always compare estimated and actual rows for accuracy

Quick Review Questions

What is the difference between EXPLAIN and EXPLAIN ANALYZE?
What does a Seq Scan mean in a query plan?
How can you tell if an index is being used?

For Instructors

Teaching Tips

Encourage students to experiment with their own queries
Use real-world examples from your database
Highlight the importance of reading plans from the bottom up

Assessment Ideas

Ask students to interpret a sample plan
Have students optimize a slow query and explain their process
Peer review: students explain plans to each other

Adaptation Notes

For beginners: focus on simple queries and plans
For advanced students: include joins, subqueries, and CTEs
Online: use screen sharing for live plan analysis