How to analyze and optimize a slow SQL query?

To analyze and optimize a slow SQL query, you should:

1. Use EXPLAIN/EXPLAIN ANALYZE: obtain the execution plan of the query to understand which operations take the most time.
2. Check indexes: ensure that appropriate indexes are used for WHERE and JOIN conditions.
3. Optimize the query:
   • Simplify complex JOINs
   • Avoid SELECT *
   • Use aggregate functions and subqueries efficiently
4. Profile the load: check if the query is blocked by other transactions.
5. Caching: consider caching frequently executed queries.

Example of using EXPLAIN in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

The output can help identify which indexes are used and where the bottlenecks are.