EXPLAIN ANALYZE for actual execution in PostgreSQL - Step-by-Step Execution

Concept Flow - EXPLAIN ANALYZE for actual execution

Start Query

↓

Parse Query

↓

Plan Query

↓

Execute Query

↓

Collect Actual Stats

↓

Display Execution Plan with Stats

↓

End

The database parses and plans the query, executes it, collects real execution stats, and then shows the plan with actual timing and row counts.

Execution Sample

PostgreSQL

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

This command runs the query and shows the actual execution plan with timing and row counts.

Execution Table

Step	Action	Details	Result
1	Parse Query	Read and check syntax	Query parsed successfully
2	Plan Query	Create execution plan	Plan created: Seq Scan on employees
3	Execute Query	Run plan to fetch rows	Rows filtered by department='Sales'
4	Collect Stats	Measure time and rows	Actual time: 0.5 ms, Rows: 10
5	Display Plan	Show plan with actual stats	Seq Scan: actual time=0.5..0.7 ms, rows=10
6	End	Finish execution	Query complete

💡 Execution ends after query runs and stats are collected and displayed

Variable Tracker

Variable	Start	After Parse	After Plan	After Execute	After Stats	Final
Query Text	EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';	Parsed	Planned	Executed	Stats Collected	Displayed
Rows Returned	N/A	N/A	N/A	10	10	10
Execution Time (ms)	N/A	N/A	N/A	0.7	0.7	0.7

Key Moments - 3 Insights

Why does EXPLAIN ANALYZE take longer than just EXPLAIN?

Because EXPLAIN ANALYZE actually runs the query to collect real execution times and row counts, as shown in execution_table steps 3 and 4.

What does 'actual time=0.5..0.7 ms' mean in the output?

It shows the start and end time of the operation during execution, meaning the step began at 0.5 ms and finished at 0.7 ms, as seen in execution_table step 5.

Why are the number of rows important in EXPLAIN ANALYZE?

Because actual rows processed help verify if the plan matches real data, helping detect inefficiencies, as tracked in variable_tracker for 'Rows Returned'.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the query actually run and fetch rows?

A Step 3

B Step 2

C Step 5

D Step 1

💡 Hint

Check the 'Action' column for 'Execute Query' in execution_table row 3.

According to variable_tracker, what is the number of rows returned after execution?

A N/A

B 0

C 10

D 5

💡 Hint

Look at the 'Rows Returned' row under 'After Execute' and later columns.

If EXPLAIN ANALYZE did not collect actual stats, which step would be missing in execution_table?

A Step 6

B Step 4

C Step 2

D Step 1

💡 Hint

Step 4 is 'Collect Stats' which gathers real execution data.

Concept Snapshot

EXPLAIN ANALYZE runs a query and shows the actual execution plan.
It includes real timing and row counts.
Syntax: EXPLAIN ANALYZE <query>;
Useful to find slow parts and optimize queries.
Shows steps: parse, plan, execute, collect stats, display.
Helps compare estimated vs actual performance.

Full Transcript

EXPLAIN ANALYZE is a command in PostgreSQL that runs a query and shows the real execution plan with timing and row counts. The process starts by parsing the query to check syntax, then planning how to run it. Next, the query is executed, and actual statistics like time taken and rows processed are collected. Finally, the plan with these real stats is displayed to the user. This helps understand how the database runs the query and where it spends time. The execution table shows each step clearly, and the variable tracker follows key values like rows returned and execution time. Beginners often wonder why EXPLAIN ANALYZE takes longer than EXPLAIN alone — this is because it runs the query fully to gather real data. The actual time values show when each step started and ended. The number of rows processed is important to verify the plan's accuracy. This visual execution helps learners see the flow from query start to finish with real performance data.