pg_stat_statements extension to find slow queries. If not enabled, enable it by adding pg_stat_statements to shared_preload_libraries in your postgresql.conf, then restart your PostgreSQL server and run:CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent, avg_time, max_time, min_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
EXPLAIN ANALYZE. Replace YOUR_QUERY with the slow query:EXPLAIN ANALYZE YOUR_QUERY;
CREATE INDEX ON table_name (column_name);
SELECT * FROM pg_stat_user_indexes JOIN pg_index USING(indexrelid) WHERE idx_scan < 50 AND indisunique IS FALSE;
VACUUM VERBOSE ANALYZE table_name;
SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY query_start desc;
postgresql.conf related to performance, such as work_mem, maintenance_work_mem, shared_buffers, and effective_cache_size based on your server's resources and workload.Let Dr. Droid create custom investigation plans for your infrastructure.
Start Free POC (15-min setup) →
