EXPLAIN ANALYZE for actual execution in PostgreSQL - Deep Dive

Overview - EXPLAIN ANALYZE for actual execution

What is it?

EXPLAIN ANALYZE is a command in PostgreSQL that shows how the database runs a query. It not only displays the planned steps but also runs the query and reports the actual time taken for each step. This helps understand the real performance of the query, not just the estimated plan.

Why it matters

Without EXPLAIN ANALYZE, you only see the database's guess about how a query will run, which can be wrong. This can lead to slow queries and wasted resources. Using EXPLAIN ANALYZE helps find bottlenecks and optimize queries, making applications faster and more efficient.

Where it fits

Before learning EXPLAIN ANALYZE, you should understand basic SQL queries and the EXPLAIN command that shows query plans without running them. After mastering EXPLAIN ANALYZE, you can learn advanced query optimization and indexing strategies.

Mental Model

Core Idea

EXPLAIN ANALYZE runs a query and reports exactly how long each step took, revealing the true performance behind the plan.

Think of it like...

It's like watching a cooking show where the chef not only shows the recipe steps but also times how long each step actually takes, so you know where the slow parts are.

┌─────────────────────────────┐
│ EXPLAIN ANALYZE Command     │
├───────────────┬─────────────┤
│ Query Plan    │ Actual Time │
├───────────────┼─────────────┤
│ Step 1        │ 10 ms       │
│ Step 2        │ 50 ms       │
│ Step 3        │ 5 ms        │
└───────────────┴─────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding Query Plans

Concept: Learn what a query plan is and how the database decides to run a query.

When you write a SQL query, the database creates a plan to decide the best way to get the data. This plan shows steps like scanning tables or joining data. EXPLAIN shows this plan without running the query.

Result

You see a list of steps the database plans to do, but no actual data or timing.

Knowing that a query plan is a roadmap helps you understand why seeing the real timing matters.

2

Foundation

Using EXPLAIN Without Execution

Concept: Learn how to use EXPLAIN to see the estimated plan without running the query.

Run EXPLAIN SELECT * FROM table; to see the plan. It shows estimated costs and rows but does not run the query or show real times.

Result

Output shows estimated steps and costs but no actual execution data.

Understanding that EXPLAIN alone only guesses performance sets the stage for why EXPLAIN ANALYZE is needed.

3

Intermediate

Running EXPLAIN ANALYZE

🤔Before reading on: do you think EXPLAIN ANALYZE runs the query or just shows the plan? Commit to your answer.

Concept: EXPLAIN ANALYZE runs the query and shows actual execution times for each step.

Run EXPLAIN ANALYZE SELECT * FROM table; The database executes the query and reports the real time spent on each step, plus the number of rows processed.

Result

Output includes actual time and rows for each step, revealing real performance.

Knowing that EXPLAIN ANALYZE runs the query helps you trust the timing data as real, not just estimates.

4

Intermediate

Interpreting Timing and Rows

🤔Before reading on: do you think more rows always mean slower steps? Commit to your answer.

Concept: Learn to read actual time and rows to find slow parts and inefficiencies.

EXPLAIN ANALYZE shows 'actual time' for start and end of each step and 'rows' processed. Large times or unexpected row counts can indicate problems like missing indexes or bad joins.

Result

You can identify which steps take the most time and why.

Understanding timing and row counts reveals where to focus optimization efforts.

5

Intermediate

Comparing Estimated vs Actual

🤔Before reading on: do you think estimated and actual times always match closely? Commit to your answer.

Concept: Compare estimated costs with actual times to spot inaccurate estimates.

EXPLAIN ANALYZE output shows estimated rows and actual rows side by side. Large differences mean the planner guessed wrong, which can cause slow queries.

Result

You see where the planner's guesses fail, guiding better indexing or query rewriting.

Knowing when estimates are wrong helps prevent surprises in query performance.

6

Advanced

Using EXPLAIN ANALYZE for Complex Queries

🤔Before reading on: do you think EXPLAIN ANALYZE can handle queries with subqueries and joins? Commit to your answer.

Concept: EXPLAIN ANALYZE works on complex queries, showing detailed timing for nested steps.

For queries with joins, subqueries, or aggregates, EXPLAIN ANALYZE breaks down each part with actual times and rows, helping find bottlenecks in complex logic.

Result

Detailed insight into which join or subquery slows the query down.

Understanding detailed breakdowns enables optimization of even complicated queries.

7

Expert

Interpreting Buffers and Planning Time

🤔Before reading on: do you think EXPLAIN ANALYZE shows only execution time or also planning and disk usage? Commit to your answer.

Concept: EXPLAIN ANALYZE can show extra info like planning time and buffer usage to diagnose deeper issues.

Using EXPLAIN (ANALYZE, BUFFERS) shows how much data was read from memory or disk, and planning time shows how long the database took to prepare the query. This helps find IO bottlenecks or planning overhead.

Result

You get a full picture of query cost including CPU, IO, and planning.

Knowing these details helps optimize not just execution but also query preparation and resource use.

Under the Hood

When you run EXPLAIN ANALYZE, PostgreSQL first creates a query plan like usual. Then it actually runs the query step by step, measuring the time spent and rows processed at each step. It collects this data during execution and reports it after finishing. This requires extra work but gives real performance data.

Why designed this way?

EXPLAIN ANALYZE was designed to provide accurate feedback on query performance because estimates alone often mislead. Running the query ensures timing is real, but it must be done carefully to avoid side effects. This tradeoff balances insight with cost.

┌───────────────┐
│ Query Input   │
└──────┬────────┘
       │
┌──────▼────────┐
│ Planner       │
│ (creates plan)│
└──────┬────────┘
       │
┌──────▼────────┐
│ Executor      │
│ (runs query,  │
│ measures time)│
└──────┬────────┘
       │
┌──────▼────────┐
│ Report Output │
│ (plan + times)│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does EXPLAIN ANALYZE run the query or just simulate it? Commit yes or no.

Common Belief: EXPLAIN ANALYZE only shows the plan without running the query.

Reality: EXPLAIN ANALYZE actually runs the query and measures real execution times.

Why it matters: Thinking it doesn't run the query can lead to unexpected side effects or long waits when using EXPLAIN ANALYZE.

Quick: Do estimated and actual times always match closely? Commit yes or no.

Common Belief: The estimated times in EXPLAIN are always accurate and close to actual times.

Reality: Estimated times are guesses and can differ greatly from actual times shown by EXPLAIN ANALYZE.

Why it matters: Relying only on estimates can cause missed performance problems and wrong optimization choices.

Quick: Does EXPLAIN ANALYZE show the time spent planning the query? Commit yes or no.

Common Belief: EXPLAIN ANALYZE only shows execution time, not planning time.

Reality: EXPLAIN ANALYZE can show planning time if requested, which can be significant for complex queries.

Why it matters: Ignoring planning time can hide delays caused before execution starts.

Quick: Does EXPLAIN ANALYZE always return the same results as the query itself? Commit yes or no.

Common Belief: EXPLAIN ANALYZE returns the query results along with the plan.

Reality: EXPLAIN ANALYZE does not return query data, only the plan and timing info.

Why it matters: Expecting data output can confuse beginners and lead to misuse.

Expert Zone

1

EXPLAIN ANALYZE can cause side effects if the query modifies data, so use it carefully on write operations.

2

The timing includes overhead from measurement itself, so very fast steps may show small extra time.

3

Buffer usage info reveals whether data was read from memory or disk, critical for diagnosing IO bottlenecks.

When NOT to use

Avoid EXPLAIN ANALYZE on queries that change data in production unless you use transaction rollbacks or test environments. For quick estimates without execution cost, use EXPLAIN alone. For very large queries, EXPLAIN ANALYZE can be slow and resource-heavy.

Production Patterns

Developers use EXPLAIN ANALYZE to profile slow queries, compare before/after optimizations, and verify index effectiveness. DBAs schedule regular checks on critical queries and use buffer info to tune caching and disk performance.

Connections

Query Optimization

EXPLAIN ANALYZE provides the real data needed to optimize queries effectively.

Understanding actual execution times helps choose the best indexes and rewrite queries for speed.

Performance Profiling in Software Engineering

Both EXPLAIN ANALYZE and software profilers measure real execution time to find bottlenecks.

Knowing how profiling works in code helps grasp why measuring real query time is crucial for databases.

Operating System I/O Scheduling

EXPLAIN ANALYZE's buffer usage relates to how the OS manages disk and memory access.

Understanding OS I/O helps interpret buffer stats and optimize database disk usage.

Common Pitfalls

#1 Running EXPLAIN ANALYZE on a data-changing query without precautions.

Wrong approach:

EXPLAIN ANALYZE DELETE FROM users WHERE active = false;

Correct approach:

BEGIN; EXPLAIN ANALYZE DELETE FROM users WHERE active = false; ROLLBACK;

Root cause:

Not realizing EXPLAIN ANALYZE executes the query, causing unintended data changes.

#2 Expecting EXPLAIN ANALYZE to return query results.

Wrong approach:

SELECT * FROM users; EXPLAIN ANALYZE SELECT * FROM users;

Correct approach:

SELECT * FROM users; -- to get data
EXPLAIN ANALYZE SELECT * FROM users; -- to get plan and timing

Root cause:

Confusing EXPLAIN ANALYZE output with query result sets.

#3 Ignoring large differences between estimated and actual rows.

Wrong approach:

Optimizing based only on EXPLAIN without ANALYZE, trusting estimates fully.

Correct approach:

Use EXPLAIN ANALYZE to compare estimates and actuals before optimizing.

Root cause:

Believing planner estimates are always accurate.

Key Takeaways

EXPLAIN ANALYZE runs a query and reports the real time spent on each step, unlike EXPLAIN which only shows estimates.

Actual execution times and row counts reveal true query performance and help identify bottlenecks.

Comparing estimated and actual data uncovers planner misestimates that can cause slow queries.

EXPLAIN ANALYZE can show extra details like buffer usage and planning time for deeper analysis.

Use EXPLAIN ANALYZE carefully on data-changing queries to avoid unintended effects.

Practice

1. What is the main purpose of using EXPLAIN ANALYZE in PostgreSQL?

easy

A. To only check the syntax of the SQL query without running it

B. To run the query and show the actual execution plan with timing details

C. To delete data from the database safely

D. To create a backup of the database

Solution

1. Step 1: Understand what EXPLAIN ANALYZE does

   It runs the SQL query and collects detailed information about how the database executes it, including timing and row counts.

2. Step 2: Compare with other options

   Options A, C, and D describe different actions unrelated to EXPLAIN ANALYZE's purpose.

3. Final Answer:

   To run the query and show the actual execution plan with timing details -> Option B

4. Quick Check:

   EXPLAIN ANALYZE = actual execution plan with timing [OK]

Hint: EXPLAIN ANALYZE runs query and shows real execution details [OK]

Common Mistakes:

• Thinking EXPLAIN ANALYZE only checks syntax
• Confusing it with backup or delete commands
• Assuming it does not run the query

2. Which of the following is the correct syntax to get the actual execution plan of a query SELECT * FROM users; using EXPLAIN ANALYZE?

easy

A. EXPLAIN SELECT * FROM users;

B. EXPLAIN RUN SELECT * FROM users;

C. EXPLAIN ANALYZE SELECT * FROM users;

D. ANALYZE EXPLAIN SELECT * FROM users;

Solution

1. Step 1: Recall the correct EXPLAIN ANALYZE syntax

   The correct syntax is to write EXPLAIN ANALYZE followed by the query to run and analyze.

2. Step 2: Check each option

   EXPLAIN SELECT * FROM users; misses ANALYZE, so it only shows estimated plan. ANALYZE EXPLAIN SELECT * FROM users; reverses keywords incorrectly. EXPLAIN RUN SELECT * FROM users; uses an invalid keyword RUN.

3. Final Answer:

   EXPLAIN ANALYZE SELECT * FROM users; -> Option C

4. Quick Check:

   EXPLAIN ANALYZE + query = correct syntax [OK]

Hint: EXPLAIN ANALYZE before query runs it and shows plan [OK]

Common Mistakes:

• Omitting ANALYZE keyword
• Swapping EXPLAIN and ANALYZE order
• Using invalid keywords like RUN

3. Given the query EXPLAIN ANALYZE SELECT * FROM orders WHERE order_id = 10;, which part of the output tells you how many rows were actually returned?

medium

A. The "Buffers" section

B. The "Estimated Rows" value in the output

C. The "Planning Time" value

D. The "Actual Rows" value in the output

Solution

1. Step 1: Understand EXPLAIN ANALYZE output fields

   "Actual Rows" shows the real number of rows returned by each step during execution.

2. Step 2: Differentiate from other fields

   "Estimated Rows" is the planner's guess before running. "Planning Time" is time spent planning. "Buffers" shows disk usage, not row count.

3. Final Answer:

   The "Actual Rows" value in the output -> Option D

4. Quick Check:

   Actual Rows = real returned rows [OK]

Hint: "Actual Rows" shows real returned rows, not estimates [OK]

Common Mistakes:

• Confusing estimated rows with actual rows
• Looking at planning time for row count
• Ignoring actual execution details

4. You run ANALYZE EXPLAIN SELECT * FROM products WHERE price > 100; but get an error: ERROR: syntax error at or near "EXPLAIN". What is the likely cause?

medium

A. You wrote ANALYZE before EXPLAIN

B. You forgot the semicolon at the end

C. You used EXPLAIN without ANALYZE

D. You ran the query without EXPLAIN

Solution

1. Step 1: Analyze the error message

   The error points to a syntax problem near "EXPLAIN" which suggests wrong keyword order.

2. Step 2: Check correct keyword order

   The correct order is EXPLAIN ANALYZE, not ANALYZE EXPLAIN. Writing ANALYZE first causes syntax error.

3. Final Answer:

   You wrote ANALYZE before EXPLAIN -> Option A

4. Quick Check:

   EXPLAIN must come before ANALYZE [OK]

Hint: EXPLAIN always comes before ANALYZE in syntax [OK]

Common Mistakes:

• Swapping EXPLAIN and ANALYZE keywords
• Missing semicolon (usually different error)
• Running query without EXPLAIN ANALYZE

5. You want to optimize a slow query joining customers and orders. Using EXPLAIN ANALYZE, you see a sequential scan on orders despite an index on customer_id. What should you check or do next?

hard

A. Check if statistics are outdated and run ANALYZE orders;

B. Drop the index on customer_id to force sequential scan

C. Rewrite the query without JOIN to avoid scans

D. Increase the database cache size to ignore indexes

Solution

1. Step 1: Understand why index might be ignored

   If statistics are outdated, the planner may wrongly choose a sequential scan instead of using the index.

2. Step 2: Use ANALYZE to update statistics

   Running ANALYZE orders; updates table statistics so planner can make better decisions.

3. Final Answer:

   Check if statistics are outdated and run ANALYZE orders; -> Option A

4. Quick Check:

   Update stats with ANALYZE to help planner use indexes [OK]

Hint: Run ANALYZE to update stats so planner uses indexes [OK]

Common Mistakes:

• Dropping useful indexes
• Ignoring statistics update
• Trying to avoid JOINs instead of fixing plan