What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the execution plan of an SQL query, that is, how the DBMS intends to execute it, which indexes to use, the order of operations, etc. This helps understand why a query might run slowly.

EXPLAIN ANALYZE not only shows the plan but also actually executes the query, collecting statistics on the execution time of each operation and the number of rows processed. This provides a more accurate view of the actual query performance.

Example:

EXPLAIN SELECT * FROM users WHERE id = 10;
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 10;

The first will show the plan, the second — the plan plus real execution metrics.

In PostgreSQL, the EXPLAIN command shows the query execution plan, that is, how the DBMS intends to retrieve data (used indexes, sequential scans, joins, etc.). It does not execute the query itself but only displays the expected plan.

EXPLAIN ANALYZE executes the query and shows the actual execution plan along with real-time and the number of rows processed at each step. This allows comparing the plan with actual performance and identifying bottlenecks.

Example:

EXPLAIN SELECT * FROM users WHERE id = 1;
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

The first shows the plan, the second — the plan with real metrics.