What is the difference between EXPLAIN and EXPLAIN ANALYZE in query performance analysis?

The EXPLAIN and EXPLAIN ANALYZE commands are used for analyzing SQL query execution but differ in detail level and impact on execution.

• EXPLAIN

  • Shows the execution plan that the query optimizer intends to use.
  • Does not execute the query itself, only estimates how it will be executed.
  • Allows seeing the order of operations, used indexes, join methods, etc.

• EXPLAIN ANALYZE

  • Executes the query and collects actual execution statistics.
  • Shows real execution times for each operation, number of processed rows, and other metrics.
  • Helps identify bottlenecks and verify how closely the plan matches actual execution.

Example in PostgreSQL:

EXPLAIN SELECT * FROM users WHERE id = 10;

EXPLAIN ANALYZE SELECT * FROM users WHERE id = 10;

The first shows the plan, the second shows the plan with actual performance metrics.

Thus, EXPLAIN is a forecast, while EXPLAIN ANALYZE provides a real report with execution details.