There is a slow SQL query. How to diagnose?

To diagnose a slow SQL query, I usually follow these steps:

1. Execution plan analysis (EXPLAIN / EXPLAIN ANALYZE) — I look at how the DBMS plans to execute the query, which indexes are used, and whether there are full table scans.

2. Index check — I ensure that there are appropriate indexes on columns involved in filters and joins.

3. Resource profiling — I monitor CPU, disk, and memory load during query execution.

4. Query optimization — I simplify the query, break it into parts, and remove redundant JOINs or subqueries.

5. Statistics check and freshness — if statistics are outdated, the plan may be inefficient.

6. DBMS parameters and configuration — sometimes memory or caching settings affect performance.

7. Testing with different data volumes — to understand how the query scales.

Example command for plan analysis in PostgreSQL:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;

This provides detailed information about execution time and operations used.