How does EXPLAIN work and what is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN is a command in SQL that shows the execution plan of a query, i.e., how the DBMS intends to process it: which indexes to use, in what order to read tables, and what operations to perform. This helps understand why a query might be slow.

EXPLAIN ANALYZE additionally executes the query itself and shows the actual execution time of each step in the plan, as well as the number of rows processed. Thus, EXPLAIN provides a theoretical plan, while EXPLAIN ANALYZE provides actual statistics with real costs.

Example in PostgreSQL:

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

The first shows the plan, the second shows the plan plus real data on time and row count.