How can you identify which SQL query is causing a decrease in database performance?

To determine which SQL query causes a decrease in database performance, you can use the following approaches:

1. Using database profilers and logs. For example, in PostgreSQL, you can enable log_min_duration_statement to log queries that run longer than a specified time.

2. EXPLAIN and EXPLAIN ANALYZE — analyzing the execution plan of queries. This helps understand which operations take a lot of time.

3. Monitoring with system views. In PostgreSQL, for instance, there is pg_stat_statements, where you can see statistics on query execution: number of calls, average time, etc.

4. Using external monitoring tools (such as pgAdmin, Percona Monitoring and Management, New Relic), which collect metrics and show the most "heavy" queries.

Example of using pg_stat_statements in PostgreSQL:

SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

This will show the 5 queries that take the most time in total, helping to identify problematic queries.