How to optimize a slow SQL query? What tools to use?

To optimize a slow SQL query, you can use the following approach:

1. Query execution plan analysis — understand which operations take the most time.

   • In PostgreSQL: EXPLAIN ANALYZE <query>
   • In MySQL: EXPLAIN <query>

2. Indexing — add or optimize indexes on columns involved in filtering, joins, and sorting.

3. Query optimization — rewrite the query, remove redundant JOINs, subqueries, and use aggregations and filters efficiently.

4. Caching — cache results if data doesn't change often.

5. Parameterization of queries — for reuse of execution plans.

6. Database profiling — identify bottlenecks.

Tools:

• EXPLAIN and EXPLAIN ANALYZE for plan analysis.
• pg_stat_statements in PostgreSQL for monitoring slow queries.
• MySQL Slow Query Log for logging slow queries.
• Profilers and APM tools (e.g., New Relic, Datadog) for performance monitoring.

Example of using EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

This will show how the DBMS executes the query and where time is spent.

Optimization is an iterative process: analyze, modify, test.