Come individuare e analizzare una query che richiede molto tempo in PostgreSQL?

Per individuare query che richiedono molto tempo in PostgreSQL, si può utilizzare la vista di sistema pg_stat_activity, che mostra le query attive correnti e il loro tempo di esecuzione. Ad esempio, la query:

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

permetterà di vedere le 5 query attive più lunghe.

Per un'analisi più approfondita, si può abilitare il logging delle query lente nel file postgresql.conf:

log_min_duration_statement = 1000  # loggare le query che durano più di 1000 ms

Dopo di che, tutte le query che superano questo tempo verranno registrate nei log.

È anche utile usare strumenti di profiling e EXPLAIN ANALYZE per analizzare il piano di esecuzione di query specifiche e individuare i colli di bottiglia.