There is a slow SELECT query that has been taking longer to execute over time. How would you diagnose the problem?

To diagnose a slow SELECT query that has become slower over time, you can follow these steps:

1. Query profiling: Use database profiling tools (e.g., EXPLAIN or EXPLAIN ANALYZE in PostgreSQL, SHOW PLAN in SQL Server) to analyze the query execution plan. This will show which operations take the most time.

2. Check indexes: Ensure that the indexes needed for the query exist and are not corrupted. Data growth might have rendered old indexes less effective.

3. Statistics and updating statistics: Check if the table statistics are up-to-date. Outdated statistics can lead to inefficient execution plans.

4. Data changes: Analyze how the volume and distribution of data have changed. Growth or distribution changes can affect performance.

5. Locks and contention: Check if the query is blocked by other transactions.

6. Caching: Perhaps data was previously in cache, and now it has to be read from disk.

7. Query optimization: Consider rewriting the query, breaking it into smaller parts, or using aggregations or filters.

Example of using EXPLAIN in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

This will show how long each part of the query takes and help identify bottlenecks.