Optimizing Queries with EXPLAIN ANALYZE

1. From Estimation to Execution: EXPLAIN ANALYZE

The EXPLAIN command shows a plan. EXPLAIN ANALYZE generates a plan, executes it, and then shows the plan annotated with the actual results of that execution. This is the difference between reading a map and actually walking the route.

The official PostgreSQL documentation provides the ground truth for this command. It's essential reading to understand the tool's full capabilities.

The core value of EXPLAIN ANALYZE is the comparison it enables between two sets of numbers for each node in the plan:

• The Estimate: (cost=... rows=...) — What the planner thought would happen.
• The Reality: (actual time=... rows=... loops=...) — What actually happened during execution.

A significant divergence between estimated rows and actual rows is the single most common reason for a poorly performing query. The planner, acting on bad information, chooses a plan that is ill-suited for the real data distribution. Your job as an engineer is to use EXPLAIN ANALYZE to find these divergences.

2. A Systematic Approach to Reading Plans

An EXPLAIN ANALYZE output can be dense. To avoid getting lost, a systematic approach is essential. The process is to identify the primary bottleneck, form a hypothesis about its cause, and then gather more evidence.

The video "A beginners guide to EXPLAIN ANALYZE" by Michael Christofides offers an excellent, practical walkthrough of this process.

Let's formalize this process:

1. Run EXPLAIN (ANALYZE, BUFFERS) on a realistic workload. The BUFFERS option provides crucial I/O statistics.
2. Identify the top-level execution time. This is your baseline. Look for Execution Time: ... ms at the very end of the output.
3. Read the plan tree from the inside out (or bottom up). Execution flows from the leaf nodes (scans) up to the root.
4. Find the most expensive node. For each node, calculate its exclusive time: node's actual time - sum of its children's actual times. The node with the highest exclusive time is likely your primary bottleneck.
5. Analyze the bottleneck node. Compare its rows (estimate) with its actual rows. Is there a large discrepancy (e.g., an order of magnitude or more)? This is your strongest clue.
6. Examine the I/O. Look at the Buffers line. shared hit means data was found in cache (fast). shared read means data was fetched from disk (slow). A high number of reads indicates an I/O-bound operation.

3. Why Estimates Go Wrong

When you see rows=10 and actual rows=100000, it's natural to ask why the planner was so wrong. The answer lies in how the optimizer works. It doesn't look at all your data; it uses statistical summaries stored in its catalog.

The CMU lecture on Query Planning and Optimization provides the theoretical underpinning for this. Understanding this theory separates a technician who can fix a slow query from an engineer who can design a system that avoids them.

These statistical models are why running the ANALYZE command (the SQL command, not the EXPLAIN option) on your tables periodically is critical for performance. It updates the planner's "map" of your data.

4. Practical Investigation

Let's apply this. Using the planner_test table from the previous lesson, we will now use EXPLAIN ANALYZE to verify our findings and uncover new insights.

Connect to your database using psql.

1. The Highly Selective Query

Run the following command:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM planner_test WHERE value = 1234;

Your output will look something like this (numbers will vary):

 Index Scan using idx_planner_test_value on planner_test ... (cost=0.42..8.44 rows=1 width=37) (actual time=0.016..0.017 rows=1 loops=1)
   Index Cond: (value = 1234)
   Buffers: shared hit=4
 Planning Time: 0.123 ms
 Execution Time: 0.035 ms

• Analysis:
  • Compare rows=1 to actual rows=1. The estimate is perfect.
  • The Execution Time is a fraction of a millisecond. Very fast.
  • Buffers: shared hit=4 means PostgreSQL found the 4 data blocks it needed already in memory. No disk I/O was required. This is an ideal scenario.

2. The Non-Selective Query

Now run the query that the planner correctly identified should use a sequential scan:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM planner_test WHERE value > 100;

The output might be:

 Seq Scan on planner_test ... (cost=0.00..1693.00 rows=98911 width=37) (actual time=0.008..8.680 rows=98901 loops=1)
   Filter: (value > 100)
   Rows Removed by Filter: 1099
   Buffers: shared read=443
 Planning Time: 0.081 ms
 Execution Time: 9.344 ms

• Analysis:
  • rows=98911 vs actual rows=98901. A fantastic estimate.
  • Execution Time is much higher, as expected.
  • Buffers: shared read=443 is the key insight here. The query had to read all 443 pages of the table from disk into memory. This confirms the I/O cost of a sequential scan.
  • Rows Removed by Filter: 1099 tells you how many rows were read but did not match the WHERE clause.

3. Investigating a Mis-estimation (Join)

This is where EXPLAIN ANALYZE truly shines. Let's create a scenario where the planner's assumptions are likely to be violated. We'll create a second table and join. The planner assumes that the values in the two tables are independent, which is often not true in real-world data.

-- Create a second table
CREATE TABLE IF NOT EXISTS planner_test_2 (
    id SERIAL PRIMARY KEY,
    test_id INT
);

-- Insert 10k rows, with test_id referencing planner_test.id but skewed
INSERT INTO planner_test_2 (test_id)
SELECT (random() * 5000)::int + 1
FROM generate_series(1, 10000);

-- Create an index and analyze
CREATE INDEX IF NOT EXISTS idx_planner_test_2_test_id ON planner_test_2(test_id);
ANALYZE planner_test_2;

Now, run a join query. We will filter for a very small, specific subset of planner_test and join it to planner_test_2. The planner will likely underestimate how many rows from planner_test_2 will match this small, specific set.

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM planner_test t1
JOIN planner_test_2 t2 ON t1.id = t2.test_id
WHERE t1.id = 42;

Look at the output carefully. You will likely see a Nested Loop join. Pay attention to the Index Scan on planner_test_2 node.

• Hypothesize: How many rows does the planner estimate will be returned from planner_test_2? (Hint: It will probably be a very small number, maybe 1).
• Observe: How many rows were actually returned? (Hint: Since we skewed our data, it will be more than the estimate, maybe actual rows=2 or actual rows=3).

This may seem like a small difference, but it's the kind of subtle mis-estimation that, when compounded in a complex 10-table join, can lead to the planner choosing a catastrophically bad plan that is orders of magnitude slower than the optimal one.

Document your findings for these three queries in your engineering journal. Note the plans, the estimated vs. actual rows, the timings, and the buffer stats. This is the core workflow of a performance engineer.

Conclusion

You have now graduated from interpreting the planner's intentions to verifying its results. This is a massive leap in capability.

Key Takeaways:

• EXPLAIN ANALYZE executes a query to show actual run times and row counts, which is the ground truth for performance analysis.
• The most common cause of a slow query is a large discrepancy between the planner's estimated row counts (rows=...) and the actual row counts (actual rows=...).
• This discrepancy arises because the planner relies on statistics and simplifying assumptions (like independence) that may not hold true for your data.
• A systematic analysis involves finding the most time-consuming node (exclusive time), checking for row count mis-estimations, and examining I/O patterns with the BUFFERS option.

You have now completed the foundational module on relational databases. You can design a schema, write complex queries, connect from Python, and now, critically, you can diagnose and understand the performance of those queries.

In the next module, we will move up the stack from the database to the application layer. You will learn to build a web API with FastAPI, creating the interface that will eventually expose the power of your Narrative Intelligence Engine to the world.