How to Read Execution Plan in PostgreSQL: Simple Guide

Use the EXPLAIN command in PostgreSQL to see the execution plan of a query. Adding ANALYZE runs the query and shows actual run times and row counts, helping you understand how PostgreSQL executes your query step-by-step.

Syntax

The basic syntax to get an execution plan is using EXPLAIN followed by your SQL query. Adding ANALYZE runs the query and shows real execution details.

• EXPLAIN [ANALYZE] your_query;
• ANALYZE shows actual time and rows processed.
• EXPLAIN alone shows estimated plan without running the query.

sql

EXPLAIN [ANALYZE] SELECT * FROM your_table WHERE condition;

Example

This example shows how to get an execution plan with actual run times for a simple query on a sample table.

sql

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, salary INT);
INSERT INTO employees (name, salary) VALUES ('Alice', 50000), ('Bob', 60000), ('Carol', 55000);

EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 52000;

Output

Seq Scan on employees  (cost=0.00..12.00 rows=2 width=36) (actual time=0.010..0.012 rows=2 loops=1)
  Filter: (salary > 52000)
  Rows Removed by Filter: 1
Planning Time: 0.123 ms
Execution Time: 0.030 ms

Common Pitfalls

Common mistakes when reading execution plans include:

• Confusing estimated rows with actual rows; ANALYZE shows actual rows.
• Ignoring the difference between EXPLAIN and EXPLAIN ANALYZE.
• Not understanding node types like Seq Scan, Index Scan, or Nested Loop.
• Overlooking the cost and time values which indicate query efficiency.

sql

/* Wrong: Using EXPLAIN without ANALYZE to check actual times */
EXPLAIN SELECT * FROM employees WHERE salary > 52000;

/* Right: Use EXPLAIN ANALYZE to see real execution details */
EXPLAIN ANALYZE SELECT * FROM employees WHERE salary > 52000;

Quick Reference

Term	Meaning
Seq Scan	Sequential scan of the whole table
Index Scan	Scan using an index for faster access
Nested Loop	Join method looping over rows
Filter	Condition applied to rows
Cost	Estimated resource usage (start..end)
Actual Time	Real time spent on operation
Rows	Number of rows processed
Loops	How many times the operation repeated

Key Takeaways

Use EXPLAIN ANALYZE to see actual execution times and row counts.

Understand key terms like Seq Scan, Index Scan, and Nested Loop in the plan.

Compare estimated costs with actual times to find query bottlenecks.

Remember EXPLAIN alone shows estimates without running the query.

Look at Filters and Rows Removed to understand data filtering.