How would you identify and analyze a slow SQL query in a system?

To identify and analyze a slow SQL query, you can follow these steps:

1. Logging and monitoring: Enable slow query logging in the database (e.g., slow query log in MySQL).
2. Using a profiler: Run a query profiler or use built-in DBMS tools (EXPLAIN, EXPLAIN ANALYZE) to analyze the execution plan.
3. Checking indexes: Ensure that the query uses indexes rather than full table scans.
4. Query optimization: Rewrite the query to reduce the number of operations, such as removing unnecessary JOINs and using aggregate functions efficiently.
5. Checking statistics and index relevance: Update statistics and rebuild indexes if necessary.

Example of using EXPLAIN in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

This will show how the DBMS executes the query and where bottlenecks occur.