How to optimize a slow SQL query? What are EXPLAIN and EXPLAIN ANALYZE?

Optimizing a slow SQL query begins with analyzing its execution plan. For this, the commands EXPLAIN and EXPLAIN ANALYZE are used.

• EXPLAIN shows the query execution plan as seen by the DBMS, without actually running the query.
• EXPLAIN ANALYZE executes the query and shows the actual time taken for each operation, helping to identify bottlenecks.

Main steps of optimization:

1. Study the execution plan, paying attention to full table scans (Seq Scan), absence of indexes.
2. Add or modify indexes to speed up searches.
3. Rewrite the query for more efficient use of indexes.
4. Reduce the amount of data processed (filtering, aggregation).

Example of using EXPLAIN in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

This will show how long the search takes and what operations are performed.