How can you identify the cause of slow SQL query performance?

To identify the cause of slow SQL query performance, the following approaches can be used:

• EXPLAIN / EXPLAIN ANALYZE: obtain the execution plan of the query to understand which operations take the most time.
• Database profiling: use built-in database tools to monitor queries and identify bottlenecks.
• Logging slow queries: enable logging of queries that run longer than a specified time.
• Index analysis: check whether indexes are used and if additional ones are needed for acceleration.
• Query optimization: simplify the query, remove unnecessary JOINs and subqueries, and use aggregate functions efficiently.
• Table statistics check: update statistics so the optimizer can build correct plans.

Example in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

This query will show how long each part of the execution takes.