EXPLAIN ANALYZE for query profiling in PostgreSQL - Deep Dive

Overview - EXPLAIN ANALYZE for query profiling

What is it?

EXPLAIN ANALYZE is a command in PostgreSQL that shows how the database executes a query. It runs the query and provides detailed timing and cost information about each step. This helps understand where time is spent during query execution.

Why it matters

Without EXPLAIN ANALYZE, it is hard to know why a query is slow or how the database processes it. This tool helps developers and DBAs find bottlenecks and optimize queries, making applications faster and more efficient.

Where it fits

Learners should know basic SQL queries and how to write SELECT statements before using EXPLAIN ANALYZE. After mastering it, they can learn advanced query optimization and indexing strategies.

Mental Model

Core Idea

EXPLAIN ANALYZE runs a query and reports exactly how PostgreSQL executes it step-by-step with timing details.

Think of it like...

It's like watching a cooking show where the chef explains each step and how long it takes, so you understand the whole recipe process.

┌─────────────────────────────┐
│ EXPLAIN ANALYZE Output      │
├─────────────────────────────┤
│ Seq Scan on table           │
│   Rows: 1000               │
│   Time: 5.2 ms             │
│ └─ Filter: condition        │
│     Rows: 100              │
│     Time: 1.1 ms           │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

What EXPLAIN Does Alone

Concept: EXPLAIN shows the query plan without running the query.

When you write EXPLAIN before a query, PostgreSQL shows the steps it plans to take to run the query. It estimates costs and rows but does not execute the query or show actual times.

Result

You get a plan with estimated costs and row counts but no real execution data.

Understanding that EXPLAIN alone only predicts helps you see why actual performance might differ.

2

Foundation

Adding ANALYZE Runs the Query

Concept: EXPLAIN ANALYZE runs the query and shows real execution details.

By adding ANALYZE, PostgreSQL executes the query and measures actual time spent on each step. This gives real data instead of estimates.

Result

You see actual row counts and timing for each operation in the query plan.

Knowing that ANALYZE runs the query means you should be careful with queries that modify data or take long.

3

Intermediate

Reading Timing and Rows Output

🤔Before reading on: do you think the 'rows' shown are estimated or actual? Commit to your answer.

Concept: EXPLAIN ANALYZE shows actual rows processed and time spent per step.

Each step in the output shows how many rows were processed and how long it took. This helps identify slow parts or unexpected row counts.

Result

You can pinpoint which part of the query is the bottleneck by comparing times and rows.

Understanding actual rows and timing reveals inefficiencies that estimates alone hide.

4

Intermediate

Understanding Cost Estimates vs Actuals

🤔Before reading on: do you think cost estimates always match actual execution time? Commit to your answer.

Concept: Cost estimates are predictions; actual times can differ due to data distribution and system load.

The planner uses cost units to guess query expense. EXPLAIN ANALYZE shows real times, which may be higher or lower than estimates.

Result

You learn to trust actual times over estimates for performance tuning.

Knowing the difference prevents misjudging query performance based on estimates alone.

5

Intermediate

Using EXPLAIN ANALYZE with Complex Queries

Concept: EXPLAIN ANALYZE works on any query, including joins and subqueries, showing detailed execution trees.

For complex queries, the output shows nested steps with timing and rows for each part. This helps break down where time is spent in multi-step operations.

Result

You get a detailed map of query execution, making it easier to optimize complex queries.

Seeing the full execution tree helps understand how different parts interact and affect performance.

6

Advanced

Interpreting Buffers and Other Extras

🤔Before reading on: do you think EXPLAIN ANALYZE shows disk and memory usage by default? Commit to your answer.

Concept: You can add options to EXPLAIN ANALYZE to see buffer usage and other internal stats.

Using EXPLAIN (ANALYZE, BUFFERS) shows how much data was read from memory or disk. This helps diagnose IO bottlenecks.

Result

You gain insight into whether slow queries are due to disk access or CPU processing.

Knowing buffer usage helps target optimizations like caching or indexing.

7

Expert

Understanding Planning vs Execution Time

🤔Before reading on: do you think planning time is included in total query time shown? Commit to your answer.

Concept: EXPLAIN ANALYZE reports separate planning and execution times.

The output shows how long PostgreSQL spent planning the query and how long it took to run. Planning can be significant for complex queries.

Result

You can decide if optimizing query structure or execution is more important.

Recognizing planning time helps avoid wasting effort optimizing execution when planning dominates.

Under the Hood

When you run EXPLAIN ANALYZE, PostgreSQL first creates a query plan using its planner. Then it executes the query step-by-step, measuring actual time and rows at each node. It collects this data and formats it into a tree structure showing each operation's cost, rows, and timing.

Why designed this way?

Separating planning and execution allows PostgreSQL to optimize queries before running them. EXPLAIN ANALYZE was designed to provide real feedback on execution, helping users understand and improve performance. Alternatives like only showing estimates were less helpful for tuning.

┌───────────────┐
│ Query Input   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Planner       │
│ (creates plan)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Executor      │
│ (runs query)  │
│ Measures time │
│ and rows      │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ EXPLAIN ANALYZE│
│ Output        │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does EXPLAIN ANALYZE always show the exact same times every run? Commit to yes or no.

Common Belief: EXPLAIN ANALYZE gives fixed, repeatable timing results.

Reality: Execution times vary due to system load, caching, and randomness in data access.

Why it matters: Expecting exact repeatability can mislead tuning efforts and cause confusion.

Quick: Does EXPLAIN ANALYZE modify data when run? Commit to yes or no.

Common Belief: EXPLAIN ANALYZE is safe and never changes data.

Reality: EXPLAIN ANALYZE runs the query fully, so if the query modifies data, it will do so.

Why it matters: Running EXPLAIN ANALYZE on UPDATE or DELETE without caution can cause unintended data changes.

Quick: Does EXPLAIN ANALYZE show the query plan before execution? Commit to yes or no.

Common Belief: EXPLAIN ANALYZE shows the plan before running the query.

Reality: It shows the plan and actual execution details only after running the query.

Why it matters: Misunderstanding this can cause confusion about when the query runs and its side effects.

Quick: Are cost estimates in EXPLAIN always accurate predictors of query speed? Commit to yes or no.

Common Belief: Cost estimates directly reflect query execution speed.

Reality: Cost units are relative and do not map directly to real time; actual execution can differ widely.

Why it matters: Relying on cost alone can lead to wrong optimization decisions.

Expert Zone

1

EXPLAIN ANALYZE output can be affected by parallel query execution, showing multiple workers and their timings.

2

The planner's cost model uses arbitrary units, so comparing costs across different queries or systems is unreliable.

3

Buffer usage statistics require explicit enabling and can reveal hidden IO bottlenecks not visible in timing alone.

When NOT to use

Avoid EXPLAIN ANALYZE on queries that modify data in production unless on a safe test copy. For quick plan inspection without execution, use EXPLAIN alone. For very large or long-running queries, consider using auto_explain or logging instead.

Production Patterns

DBAs use EXPLAIN ANALYZE routinely to diagnose slow queries, often combining it with indexing, vacuuming, and rewriting queries. It is also used in automated monitoring tools to catch regressions.

Connections

Query Optimization

EXPLAIN ANALYZE provides the data needed to optimize queries effectively.

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

Performance Profiling in Software Engineering

Both involve measuring where time is spent during execution to find bottlenecks.

Knowing how profiling works in software helps grasp why EXPLAIN ANALYZE measures query steps and timing.

Operating System Process Scheduling

Just as OS schedules CPU time for processes, EXPLAIN ANALYZE shows how PostgreSQL schedules work for query steps.

Recognizing resource allocation patterns in OS helps understand query execution timing and parallelism.

Common Pitfalls

#1 Running EXPLAIN ANALYZE on a data-changing query in production without a transaction rollback.

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 fully, causing unintended data changes.

#2 Expecting EXPLAIN ANALYZE output to be identical every run.

Wrong approach:

Running EXPLAIN ANALYZE multiple times and assuming times must match exactly.

Correct approach:

Run EXPLAIN ANALYZE multiple times and consider average or range of timings.

Root cause:

Misunderstanding that execution time varies due to caching, load, and randomness.

#3 Using EXPLAIN without ANALYZE to measure actual query speed.

Wrong approach:

EXPLAIN SELECT * FROM orders WHERE id = 123;

Correct approach:

EXPLAIN ANALYZE SELECT * FROM orders WHERE id = 123;

Root cause:

Confusing estimated plan costs with real execution times.

Key Takeaways

EXPLAIN ANALYZE runs a query and reports detailed timing and row counts for each step, revealing real execution behavior.

It helps identify slow parts of queries by comparing actual times and rows processed, not just estimates.

Because it executes the query, EXPLAIN ANALYZE can change data if used on modifying statements, so use with care.

Understanding the difference between planning and execution time guides better optimization decisions.

Advanced options like buffer usage reveal deeper insights into IO performance beyond timing alone.

Practice

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

easy

A. To create a new database

B. To delete data from a table

C. To show how a query runs and how long each step takes

D. To backup the database

Solution

1. Step 1: Understand the function of EXPLAIN ANALYZE

   It runs the query and shows the execution plan with timing details.

2. Step 2: Compare options with this function

   Only To show how a query runs and how long each step takes describes showing query steps and timing, others are unrelated.

3. Final Answer:

   To show how a query runs and how long each step takes -> Option C

4. Quick Check:

   EXPLAIN ANALYZE = Query profiling [OK]

Hint: EXPLAIN ANALYZE = see query steps and time [OK]

Common Mistakes:

• Thinking EXPLAIN ANALYZE modifies data
• Confusing EXPLAIN ANALYZE with backup commands
• Assuming it creates or deletes databases

2. Which of the following is the correct syntax to run EXPLAIN ANALYZE on a query that selects all rows from a table named users?

easy

A. EXPLAIN ANALYZE SELECT * FROM users;

B. ANALYZE EXPLAIN SELECT * FROM users;

C. EXPLAIN SELECT * FROM users ANALYZE;

D. SELECT EXPLAIN ANALYZE * FROM users;

Solution

1. Step 1: Recall the correct order of EXPLAIN ANALYZE

   The correct syntax is EXPLAIN ANALYZE followed by the query.

2. Step 2: Check each option's syntax

   Only EXPLAIN ANALYZE SELECT * FROM users; correctly places EXPLAIN ANALYZE before the SELECT statement.

3. Final Answer:

   EXPLAIN ANALYZE SELECT * FROM users; -> Option A

4. Quick Check:

   EXPLAIN ANALYZE + query = EXPLAIN ANALYZE SELECT * FROM users; [OK]

Hint: EXPLAIN ANALYZE always comes before the query [OK]

Common Mistakes:

• Swapping EXPLAIN and ANALYZE keywords
• Placing ANALYZE after the query
• Trying to SELECT EXPLAIN ANALYZE as a column

3. Given the output snippet from EXPLAIN ANALYZE for a query:

Seq Scan on products  (cost=0.00..35.50 rows=255 width=12) (actual time=0.010..0.200 rows=255 loops=1)

What does Seq Scan mean in this context?

medium

A. The query used an index to find rows quickly

B. The query used a sequential scan reading all rows in the table

C. The query failed to execute

D. The query only scanned the first 10 rows

Solution

1. Step 1: Understand 'Seq Scan' in EXPLAIN output

   'Seq Scan' means scanning the whole table row by row without using an index.

2. Step 2: Match this meaning to options

   The query used a sequential scan reading all rows in the table correctly describes a sequential scan reading all rows; others are incorrect.

3. Final Answer:

   The query used a sequential scan reading all rows in the table -> Option B

4. Quick Check:

   Seq Scan = full table scan [OK]

Hint: Seq Scan means full table read, no index used [OK]

Common Mistakes:

• Thinking Seq Scan uses an index
• Assuming Seq Scan means query error
• Believing Seq Scan reads only part of table

4. You run EXPLAIN ANALYZE SELECT * FROM orders WHERE id = 10; but get an error: ERROR: syntax error at or near "ANALYZE". What is the likely cause?

medium

A. You need to run ANALYZE separately before EXPLAIN

B. The table orders does not exist

C. You forgot a semicolon at the end

D. You wrote ANALYZE EXPLAIN instead of EXPLAIN ANALYZE

Solution

1. Step 1: Check the error message and syntax

   The error points to a syntax problem near ANALYZE, likely wrong keyword order.

2. Step 2: Identify correct keyword order

   The correct command is EXPLAIN ANALYZE, not ANALYZE EXPLAIN, so You wrote ANALYZE EXPLAIN instead of EXPLAIN ANALYZE fits.

3. Final Answer:

   You wrote ANALYZE EXPLAIN instead of EXPLAIN ANALYZE -> Option D

4. Quick Check:

   EXPLAIN must come before ANALYZE [OK]

Hint: EXPLAIN always before ANALYZE in command [OK]

Common Mistakes:

• Swapping EXPLAIN and ANALYZE keywords
• Assuming missing semicolon causes this error
• Thinking ANALYZE must run separately first

5. You want to optimize a slow query. After running EXPLAIN ANALYZE, you see a nested loop join with very high actual time. What should you do to improve performance?

hard

A. Create indexes on the join columns to speed up lookups

B. Remove all indexes to force sequential scans

C. Rewrite the query without a WHERE clause

D. Increase the number of loops in the query

Solution

1. Step 1: Understand nested loop join with high time

   High time in nested loops often means many repeated scans; indexes can help reduce this.

2. Step 2: Choose the best optimization

   Creating indexes on join columns helps the database find matching rows faster, improving performance.

3. Final Answer:

   Create indexes on the join columns to speed up lookups -> Option A

4. Quick Check:

   Indexes speed up joins with nested loops [OK]

Hint: Add indexes on join keys to speed nested loops [OK]

Common Mistakes:

• Removing indexes which slows queries
• Removing WHERE clause losing filtering
• Trying to increase loops which is not a setting