PostgreSQL Slow Queries: How to Find and Fix Them

PostgreSQL Slow Queries: How to Find and Fix Them Your app is slow. You've ruled out the server, the network, the app code. It's the database. Here's how to find and fix slow PostgreSQL queries systematically. Step 1: Enable Slow Query Logging -- Check current settings SHOW log_min_duration_statement ; SHOW log_directory ; -- Enable logging for queries over 1 second ALTER SYSTEM SET log_min_duration_statement = 1000 ; SELECT pg_reload_conf (); Or in postgresql.conf : log_min_duration_statement = 1000 # milliseconds log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' Now any query taking over 1 second appears in your PostgreSQL logs: sudo tail -f /var/log/postgresql/postgresql- * .log | grep duration Step 2: Find Slow Queries with pg_stat_statements -- Enable the extension (once) CREATE EXTENSION IF NOT EXISTS pg_stat_statements ; -- Find your worst queries SELECT round ( mean_exec_time :: numeric , 2 ) AS avg_ms , calls , round ( total_exec_time :: numeric , 2 ) AS tota