What Is EXPLAIN ANALYZE in PostgreSQL?

EXPLAIN ANALYZE is a powerful command that shows how PostgreSQL executes a query. It reveals whether indexes are used, whether sequential scans occur, and where performance bottlenecks exist.

Basic Example

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

Understanding the Output

Term	Meaning
Seq Scan	Sequential scan (full table scan)
Index Scan	Using index for lookup
Cost	Planner’s estimated execution cost
Actual Time	Real execution time

Sequential Scan vs Index Scan

A sequential scan reads every row in a table. This is inefficient for large datasets. An index scan reads only relevant rows using an index.

Detecting Performance Problems

High actual time compared to cost
Sequential scans on large tables
Large number of rows removed by filter

Improving Query Performance

CREATE INDEX idx_users_email ON users(email);

After adding an index, run EXPLAIN ANALYZE again to compare performance improvements.

Best Practice

Always analyze slow queries using EXPLAIN ANALYZE before modifying configuration or scaling hardware. Query optimization should be the first step.