Optimizing PostgreSQL chevron_right Understanding Costs in EXPLAIN: Demystifying PostgreSQL Cost Numbers

Understanding Costs in EXPLAIN: Demystifying PostgreSQL Cost Numbers

calendar_today schedule 6 min read

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

  • Interpret the cost numbers in PostgreSQL EXPLAIN output
  • Distinguish between startup cost and total cost
  • Understand how PostgreSQL estimates costs and why they can be inaccurate

1. Welcome and Introduction (5 minutes)

Icebreaker Activity

Have you ever wondered what those mysterious numbers in parentheses mean when you run EXPLAIN in PostgreSQL? Why does the planner sometimes choose a full table scan over an index? Today, you’ll learn to read and question the cost numbers that drive PostgreSQL’s decisions!

Lesson Overview

  • Duration: 90 minutes
  • Prerequisites: Completed Day 1, basic understanding of query plans
  • Materials Needed: PostgreSQL database, SQL client

2. Learning Objectives and Session Outline (5 minutes)

What We’ll Cover Today:

  1. What are cost numbers in EXPLAIN?
  2. Startup cost vs. total cost
  3. How PostgreSQL estimates costs (disk I/O, CPU, etc.)
  4. Why cost estimates can be wrong
  5. Hands-on practice and analysis
  6. Common pitfalls and troubleshooting

Success Criteria:

By the end of this session, you should be able to explain what the cost numbers mean and use them to identify expensive operations in your query plans.

3. Foundation Knowledge (15-20 minutes)

Core Concepts

What Are Cost Numbers?

When you run EXPLAIN, each plan node shows two numbers: the startup cost and the total cost. These are PostgreSQL’s internal estimates of how expensive each step will be.

Real-World Analogy: Think of cost numbers like a GPS estimating travel time for each segment of your trip. The planner adds up the time for each route and picks the fastest.

Why This Matters:

  • Cost numbers determine which plan PostgreSQL chooses
  • Understanding them helps you spot slow steps and optimize queries

Startup Cost vs. Total Cost

  • Startup Cost: The estimated cost to start producing the first row (e.g., reading the first block from disk)
  • Total Cost: The estimated cost to process all rows for that node

How Are Costs Calculated?

  • Disk I/O: Reading from disk is expensive; sequential reads are cheaper than random reads
  • CPU: Processing each row (filters, calculations)
  • Other Factors: Memory usage, parallelism, network (for remote tables)

Key Terminology

  • Startup Cost: Cost to produce the first row
  • Total Cost: Cost to produce all rows
  • Cost Units: Arbitrary units, but typically 1.0 = cost of a sequential page read
  • Row Estimate: Planner’s guess at how many rows will be processed

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

Understanding Cost Numbers in EXPLAIN

A typical EXPLAIN output looks like this:

Seq Scan on users  (cost=0.00..35.50 rows=5 width=32)
  • 0.00 is the startup cost
  • 35.50 is the total cost
  • rows=5 is the estimated number of rows

Practical Applications

  • Use cost numbers to find the most expensive step in a plan
  • Compare different plans to see which is cheaper
  • Tune queries and indexes to reduce total cost

Common Misconceptions

  1. Misconception: “Lower cost always means faster queries.” Reality: Cost is an estimate; real performance may differ, especially if statistics are outdated.
  2. Misconception: “Cost units are milliseconds or seconds.” Reality: Cost units are arbitrary and not directly tied to time.

5. Live Coding Demonstration (20-25 minutes)

Example 1: Basic Cost Analysis

EXPLAIN SELECT * FROM users WHERE id = 42;

Explanation:

  • If there’s an index on id, the plan might show a lower total cost for Index Scan than for Seq Scan.

Example 2: Comparing Costs With and Without Indexes

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

-- Add index
CREATE INDEX idx_orders_created_at ON orders (created_at);
EXPLAIN SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';

Key Points:

  • Adding an index should lower the total cost for queries filtering on created_at
  • Compare the cost numbers before and after

Example 3: Startup vs. Total Cost in Aggregates

EXPLAIN SELECT COUNT(*) FROM users WHERE active = true;
  • Aggregates may have a higher startup cost if they need to scan many rows before producing a result

Debugging Demonstration

Show how outdated statistics can lead to bad cost estimates:

-- After large data change, but before ANALYZE
EXPLAIN SELECT * FROM users WHERE status = 'inactive';

-- Run ANALYZE to update statistics
ANALYZE users;
EXPLAIN SELECT * FROM users WHERE status = 'inactive';

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

Exercise 1: Basic Practice (10 minutes)

Objective: Identify the most expensive operation in a query plan

Instructions:

  1. Run EXPLAIN on a query with multiple joins or filters
  2. Look at the cost numbers for each node
  3. Identify which node has the highest total cost

Starter Code:

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

Solution:

-- Example output (your plan will differ)
Hash Join  (cost=35.00..120.00 rows=100 width=64)
  -> Seq Scan on users  (cost=0.00..40.00 rows=1000 width=32)
  -> Seq Scan on orders  (cost=0.00..60.00 rows=1000 width=32)

Exercise 2: Intermediate Challenge (15 minutes)

Objective: See how changing a query or adding an index affects cost

Requirements:

  • Run a query and note the cost
  • Add an index or change the query
  • Rerun and compare the costs

Hints:

  • Try adding an index on a filter column
  • Try changing a JOIN to a WHERE IN or vice versa

Exercise 3: Creative Application (15 minutes)

Objective: Experiment with cost parameters

Challenge:

  • Change planner parameters (e.g., random_page_cost, seq_page_cost) using SET LOCAL
  • Observe how the plan and costs change

Extension Ideas:

  • Try with different table sizes
  • Experiment with parallel query settings

7. Common Pitfalls and Troubleshooting (10 minutes)

Frequent Mistakes

  1. Problem: Assuming cost numbers are always accurate Cause: Outdated statistics or unusual data distribution Solution: Run ANALYZE regularly and check for data skew

  2. Problem: Misinterpreting cost units as time Cause: Cost units are not time-based Solution: Use EXPLAIN ANALYZE for real timing

Debugging Tips

  • Always update statistics after large data changes
  • Compare cost numbers before and after query/index changes
  • Use EXPLAIN ANALYZE to see real execution time

8. Best Practices and Professional Tips (5 minutes)

Industry Standards

  • Use cost numbers to guide, not dictate, optimization
  • Keep statistics up to date
  • Understand your workload and data distribution

Next Steps in Your Learning Journey

  • Learn about buffer usage and I/O analysis (see Day 3)
  • Explore how planner parameters affect costs
  • Practice with real-world queries

9. Summary and Review (5 minutes)

Key Takeaways

  1. Cost numbers drive the planner’s decisions
  2. Startup and total cost help you find expensive steps
  3. Cost estimates are only as good as your statistics

Quick Review Questions

  1. What is the difference between startup cost and total cost?
  2. Why might cost estimates be wrong?
  3. How can you improve the accuracy of cost estimates?

For Instructors

Teaching Tips

  • Use real query plans from your database
  • Show how cost numbers change with indexes and statistics
  • Emphasize the difference between cost and real execution time

Assessment Ideas

  • Have students identify the most expensive node in a plan
  • Ask students to explain why a plan changed after adding an index
  • Peer review: students compare cost numbers for different queries

Adaptation Notes

  • For beginners: focus on simple queries and cost interpretation
  • For advanced students: include planner parameter tuning and parallelism
  • Online: use screen sharing to compare plans before and after changes