How to identify and analyze a long-running query in PostgreSQL?

To identify long-running queries in PostgreSQL, you can use the system view pg_stat_activity, which shows current active queries and their execution times. For example, the query:

SELECT pid, now() - query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC
LIMIT 5;

will display the 5 longest active queries.

For more in-depth analysis, you can enable slow query logging in postgresql.conf:

log_min_duration_statement = 1000  # log queries longer than 1000 ms

After that, all queries exceeding the specified duration will be recorded in the logs.

It is also useful to use profiling tools and EXPLAIN ANALYZE to analyze the execution plan of specific queries and identify bottlenecks.