Can you explain the principle of the EXPLAIN ANALYZE command in SQL and how it is used for query optimization?

The EXPLAIN ANALYZE command in SQL is used to analyze the execution of a query. It not only shows the execution plan like EXPLAIN, but actually executes the query and provides detailed statistics on time and the number of rows processed at each stage.

This helps to understand which parts of the query are slow and where optimization can be applied, such as adding indexes or rewriting the query.

Example of usage in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

The output will show how long each step took, how many rows were processed, helping to identify bottlenecks in the query.