EXPLAIN output reading in PostgreSQL - Step-by-Step Execution

Concept Flow - EXPLAIN output reading

Start Query

↓

Run EXPLAIN

↓

Get Query Plan

↓

Read Plan Nodes

↓

Understand Cost & Rows

↓

Interpret Output

↓

Optimize Query if needed

The EXPLAIN command runs a query plan without executing it, showing how PostgreSQL plans to run the query step-by-step.

Execution Sample

PostgreSQL

EXPLAIN SELECT * FROM employees WHERE salary > 50000;

Shows the query plan for selecting employees with salary over 50000.

Execution Table

Step	Plan Node	Cost (Start..End)	Rows Estimated	Description
1	Seq Scan on employees	0.00..35.50	10	Scan all rows in employees table
2	Filter	-	-	Only rows where salary > 50000
3	Output	-	-	Return selected rows
4	End	-	-	Query plan complete

💡 All plan nodes processed; EXPLAIN output fully read.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Current Plan Node	None	Seq Scan on employees	Filter applied	Output prepared	Complete
Rows Estimated	Unknown	10	10 filtered	10 output	10 final

Key Moments - 3 Insights

Why does the cost show two numbers like 0.00..35.50?

The first number is the startup cost before output begins, the second is the total cost to finish scanning. See execution_table row 1.

What does 'Rows Estimated' mean in the EXPLAIN output?

It is PostgreSQL's guess of how many rows each step will process or output, not the actual count. See execution_table rows 1 and 2.

Why is there a 'Filter' step after the scan?

Because the scan reads all rows, then the filter removes rows not matching the condition. This is shown in execution_table row 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the estimated number of rows after the Seq Scan?

A 10

B 35.50

C 0.00

D Unknown

💡 Hint

Check the 'Rows Estimated' column in execution_table row 1.

At which step does the filter condition get applied according to the execution_table?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look at the 'Plan Node' column for the 'Filter' entry in execution_table.

If the query had an index scan instead of a sequential scan, which plan node would change in the execution_table?

A Filter

B Seq Scan on employees

C Output

D End

💡 Hint

The scan method is shown in the first plan node in execution_table row 1.

Concept Snapshot

EXPLAIN shows how PostgreSQL plans to run a query.
It lists plan nodes like scans, filters, outputs.
Costs show startup and total effort estimates.
Rows estimated are guesses, not actual counts.
Reading EXPLAIN helps find slow parts to optimize.

Full Transcript

The EXPLAIN command in PostgreSQL shows the query plan without running the query. It lists steps like scanning tables, filtering rows, and outputting results. Each step has a cost estimate showing how much work it takes. The plan also estimates how many rows each step will handle. By reading EXPLAIN output, you can understand how the database will execute your query and find ways to make it faster.