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.

Practice

1. What does the EXPLAIN command in PostgreSQL primarily show?

easy

A. How PostgreSQL plans to execute a query

B. The exact data returned by the query

C. The syntax errors in the query

D. The database schema structure

Solution

1. Step 1: Understand the purpose of EXPLAIN

   EXPLAIN shows the query plan, which is how PostgreSQL intends to run the query.

2. Step 2: Differentiate from other commands

   It does not show actual data or errors, only the plan.

3. Final Answer:

   How PostgreSQL plans to execute a query -> Option A

4. Quick Check:

   EXPLAIN = query plan [OK]

Hint: EXPLAIN = query plan, not data or errors [OK]

Common Mistakes:

• Thinking EXPLAIN shows query results
• Confusing EXPLAIN with syntax error checks
• Assuming EXPLAIN shows database schema

2. Which of the following is the correct syntax to get the query plan for SELECT * FROM users; in PostgreSQL?

easy

A. EXPLAIN SELECT * FROM users;

B. EXPLAIN ANALYZE users SELECT *;

C. EXPLAIN FROM users SELECT *;

D. ANALYZE EXPLAIN SELECT * FROM users;

Solution

1. Step 1: Recall correct EXPLAIN syntax

   The correct syntax is EXPLAIN followed by the query.

2. Step 2: Check each option

   EXPLAIN SELECT * FROM users; matches the correct syntax. Others mix keywords incorrectly.

3. Final Answer:

   EXPLAIN SELECT * FROM users; -> Option A

4. Quick Check:

   EXPLAIN + query = correct syntax [OK]

Hint: EXPLAIN always precedes the query [OK]

Common Mistakes:

• Placing ANALYZE before EXPLAIN
• Using FROM before SELECT incorrectly
• Mixing keywords in wrong order

3. Given the EXPLAIN output below for SELECT * FROM orders WHERE customer_id = 5;, what does the line Index Scan using idx_customer_id on orders indicate?

medium

A. PostgreSQL is scanning the entire orders table

B. PostgreSQL is using an index to find matching rows

C. PostgreSQL is performing a sequential scan

D. PostgreSQL is creating a new index during query

Solution

1. Step 1: Understand 'Index Scan' meaning

   An Index Scan means PostgreSQL uses an index to quickly find rows matching the condition.

2. Step 2: Compare with other scan types

   Sequential scan means scanning all rows, which is not the case here.

3. Final Answer:

   PostgreSQL is using an index to find matching rows -> Option B

4. Quick Check:

   Index Scan = use index [OK]

Hint: 'Index Scan' means index used, not full table scan [OK]

Common Mistakes:

• Confusing Index Scan with Sequential Scan
• Thinking index is created during query
• Assuming full table scan always happens

4. You run EXPLAIN ANALYZE SELECT * FROM products WHERE price > 100; but get an error saying "relation 'products' does not exist." What is the likely cause?

medium

A. EXPLAIN ANALYZE cannot be used with WHERE clauses

B. The query syntax is incorrect for EXPLAIN ANALYZE

C. The table 'products' does not exist in the current database

D. You forgot to commit the transaction

Solution

1. Step 1: Analyze the error message

   The error says the table 'products' does not exist, meaning PostgreSQL cannot find it.

2. Step 2: Check other options

   EXPLAIN ANALYZE works with WHERE clauses and the syntax is correct. Committing transaction is unrelated.

3. Final Answer:

   The table 'products' does not exist in the current database -> Option C

4. Quick Check:

   Relation not found = missing table [OK]

Hint: Check table existence if 'relation does not exist' error appears [OK]

Common Mistakes:

• Assuming EXPLAIN ANALYZE disallows WHERE
• Blaming syntax when table is missing
• Thinking commit affects table visibility

5. You want to optimize a slow query. The EXPLAIN ANALYZE output shows a Seq Scan on a large table with a filter on a column. What is the best next step to improve performance?

hard

A. Drop the table and recreate it

B. Rewrite the query without the filter

C. Increase the work_mem setting

D. Create an index on the filtered column

Solution

1. Step 1: Understand Seq Scan impact

   A Seq Scan reads all rows, which is slow on large tables when filtering.

2. Step 2: Use index to speed filtering

   Creating an index on the filtered column lets PostgreSQL quickly find matching rows, avoiding full scan.

3. Final Answer:

   Create an index on the filtered column -> Option D

4. Quick Check:

   Seq Scan slow? Add index [OK]

Hint: Seq Scan slow? Add index on filter column [OK]

Common Mistakes:

• Removing filter instead of indexing
• Changing memory settings without indexing
• Dropping table unnecessarily