How to Use EXPLAIN in PostgreSQL for Query Analysis

Use the EXPLAIN command in PostgreSQL before your SQL query to see the execution plan the database will use. This helps you understand how PostgreSQL processes your query and identify performance issues.

Syntax

The basic syntax of EXPLAIN is simple: place EXPLAIN before any SQL query to get its execution plan. You can add options like ANALYZE to run the query and show actual run times.

• EXPLAIN [ANALYZE] [VERBOSE] your_query;
• ANALYZE runs the query and shows real execution times.
• VERBOSE gives more detailed information.

sql

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

Example

This example shows how to use EXPLAIN ANALYZE to see the actual execution plan and timing for a query selecting employees from the Sales department.

sql

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

Output

Seq Scan on employees  (cost=0.00..35.50 rows=5 width=244) (actual time=0.010..0.020 rows=3 loops=1)
  Filter: ((department)::text = 'Sales'::text)
Planning Time: 0.100 ms
Execution Time: 0.030 ms

Common Pitfalls

One common mistake is using EXPLAIN without ANALYZE, which only shows estimated costs, not actual run times. Another is running EXPLAIN ANALYZE on queries that modify data without understanding it will execute the query, possibly changing data.

Also, very complex queries can produce large output that is hard to read without tools.

sql

/* Wrong: Only estimated plan, no real timing */
EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

/* Right: Shows actual execution times */
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';

Quick Reference

Option	Description
EXPLAIN	Shows the estimated execution plan without running the query
EXPLAIN ANALYZE	Runs the query and shows actual execution times and plan
EXPLAIN VERBOSE	Shows detailed information about the plan nodes
EXPLAIN (BUFFERS)	Includes buffer usage statistics in the output
EXPLAIN (COSTS FALSE)	Hides estimated costs in the output

Key Takeaways

Use EXPLAIN before a query to see how PostgreSQL plans to execute it.

Add ANALYZE to run the query and get real execution times.

Be careful with EXPLAIN ANALYZE on data-changing queries as it executes them.

Use options like VERBOSE and BUFFERS for more detailed insights.

Reading the output helps identify slow parts and optimize queries.