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.