What are EXPLAIN and EXPLAIN ANALYZE in PostgreSQL, and what is the difference?

In PostgreSQL, the EXPLAIN command is used to display the execution plan of an SQL query, that is, how the DBMS intends to execute it. It shows what operations will be performed (e.g., sequential scan, index scan, joins), in what order, and with what estimated costs.

EXPLAIN ANALYZE additionally executes the query itself and shows the actual execution time of each operation and the number of rows processed. This allows comparing the plan with the actual execution and identifying bottlenecks.

Main differences:

• EXPLAIN shows only the estimated plan and costs, without executing the query.
• EXPLAIN ANALYZE executes the query and shows real metrics, which is useful for precise performance analysis.

Example:

EXPLAIN SELECT * FROM users WHERE id = 10;

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 10;