EXPLAIN ANALYZE for query profiling in PostgreSQL - Step-by-Step Execution

Concept Flow - EXPLAIN ANALYZE for query profiling

Start Query

↓

Parse Query

↓

Plan Query

↓

Execute Query

↓

Collect Timing & Rows

↓

Display Execution Plan with Stats

↓

End

The query is parsed, planned, executed, and then detailed timing and row counts are collected and shown.

Execution Sample

PostgreSQL

EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';

This runs the query and shows the detailed execution plan with actual run times and rows processed.

Execution Table

Step	Action	Details	Timing (ms)	Rows Processed
1	Seq Scan on employees	Filter: department = 'Sales'	0.5	3
2	Output Rows	All columns for filtered rows	0.1	3
3	Total Execution	Sum of all steps	0.7	3
4	Exit	Query execution complete with results

💡 Query executed fully; timings and rows reflect actual run performance.

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
Rows Processed	0	3	3	3
Time Elapsed (ms)	0	0.5	0.6	0.7

Key Moments - 2 Insights

Why does EXPLAIN ANALYZE show actual rows processed instead of estimated rows?

Because EXPLAIN ANALYZE runs the query and collects real execution data, unlike EXPLAIN alone which only shows estimates (see execution_table rows 1 and 3).

What does the timing value represent in each step?

It shows how many milliseconds that step took during execution, helping identify slow parts (see execution_table Timing column).

Visual Quiz - 3 Questions

Look at the execution table, how many rows were processed during the Seq Scan step?

A 5

B 3

C 0

D Unknown

💡 Hint

Check the 'Rows Processed' column in row 1 of the execution_table.

At which step does the total execution time get reported?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look for the row labeled 'Total Execution' in the execution_table.

If the query had no matching rows, what would the 'Rows Processed' value be at Step 1?

A 1

B 3

C 0

D Cannot tell

💡 Hint

Rows Processed shows actual rows found; no matches means zero (see variable_tracker).

Concept Snapshot

EXPLAIN ANALYZE runs a query and shows the real execution plan.
It reports actual time spent and rows processed per step.
Helps find slow parts by timing each operation.
Unlike EXPLAIN alone, it executes the query.
Use it to profile and optimize queries.

Full Transcript

EXPLAIN ANALYZE is a PostgreSQL command that runs a query and shows detailed information about how the database executed it. The process starts by parsing and planning the query, then executing it while collecting timing and row count data. The output shows each step, how long it took, and how many rows were processed. This helps users understand which parts of the query are slow or costly. Unlike EXPLAIN alone, EXPLAIN ANALYZE runs the query for real, so the data is accurate. Beginners often wonder why the rows processed differ from estimates; this is because EXPLAIN ANALYZE shows actual results. Timing values help identify bottlenecks. If no rows match, the rows processed will be zero. This tool is essential for profiling and improving query performance.

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