How to Use EXPLAIN ANALYZE in PostgreSQL for Query Analysis

Use EXPLAIN ANALYZE before your SQL query in PostgreSQL to see the actual execution plan along with timing details. It runs the query and shows how PostgreSQL executes it step-by-step, helping you understand and optimize query performance.

Syntax

The basic syntax of EXPLAIN ANALYZE is simple. You write EXPLAIN ANALYZE followed by the SQL query you want to analyze. PostgreSQL runs the query and returns the execution plan with real run times.

• EXPLAIN ANALYZE: Runs the query and shows the actual execution plan with timing.
• SQL query: The query you want to analyze.

sql

EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;

Example

This example shows how to use EXPLAIN ANALYZE to analyze a simple SELECT query on a sample table named employees. It helps you see how PostgreSQL scans the table and how long each step takes.

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.012..0.015 rows=3 loops=1)
  Filter: ((department)::text = 'Sales'::text)
  Rows Removed by Filter: 7
Planning Time: 0.123 ms
Execution Time: 0.045 ms

Common Pitfalls

Some common mistakes when using EXPLAIN ANALYZE include:

• Using EXPLAIN without ANALYZE only shows estimated plans, not actual run times.
• Running EXPLAIN ANALYZE on queries that modify data will actually execute those changes, which might be unintended.
• Not having proper indexes can lead to slow query plans, which EXPLAIN ANALYZE will reveal.

Always be careful when running EXPLAIN ANALYZE on UPDATE, DELETE, or INSERT queries in production.

sql

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

/* Right: Actual execution with timing */
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Sales';

Quick Reference

Command	Description
EXPLAIN	Shows estimated query plan without running the query
EXPLAIN ANALYZE	Runs the query and shows actual execution plan with timing
EXPLAIN (ANALYZE, BUFFERS)	Includes buffer usage details in the output
EXPLAIN (VERBOSE)	Shows detailed information about the plan nodes

Key Takeaways

Use EXPLAIN ANALYZE to see the real execution plan and timing of your query in PostgreSQL.

EXPLAIN alone shows only estimated plans without running the query.

Be cautious running EXPLAIN ANALYZE on queries that change data as it executes them.

Look for slow steps in the output to find where to optimize your query.

Use options like BUFFERS or VERBOSE for more detailed analysis.