These are the list of PostgreSQL queries which help me a lot over the time to troubleshoot problems with the database.
1. Find out which queries running currently
SELECT pid, client_addr, query, state FROM pg_stat_activity;
2. Table access stat
This query is really helpful when you want to find out which table is most frequent accessed and by which way. Ex: sequence scan or index scan.
SELECT schemaname, relname, seq_scan, idx_scan, cast(idx_scan AS numeric) / (idx_scan + seq_scan) AS idx_scan_pct FROM pg_stat_user_tables WHERE (idx_scan + seq_scan) > 0 ORDER BY idx_scan_pct;
3. Find out missing indexes
Well, index is one of the most crucial thing which make the database fast or slow. So check for missing indexes are super important. And we can doing that with simple query like below.
SELECT relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan> 0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan FROM pg_stat_all_tables WHERE schemaname='public' AND pg_relation_size(relname::regclass) > 80000 ORDER BY too_much_seq DESC;
4. Find out unused indexes
Well, indexes are great however over indexing will slow the database down. So how we know that we’re over indexing. Ok, we can use the following query.
SELECT relid::regclass AS table, indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size, idx_tup_read, idx_tup_fetch, idx_scan FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid) WHERE idx_scan = 0;
5. Find out duplicate indexes
Over indexing is one of the problem, and duplicate index also another problem. We can find that out with the following query.
SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS size, (array_agg(idx)) AS idx1, (array_agg(idx)) AS idx2, (array_agg(idx)) AS idx3, (array_agg(idx)) AS idx4 FROM (SELECT indexrelid::regclass AS idx, (indrelid::text ||E'\n' || indclass::text ||E'\n'|| indkey::text ||E'\n'|| coalesce(indexprs::text,'') || E'\n' || coalesce(indpred::text,'')) AS KEY FROM pg_index) sub GROUP BY KEY HAVING count(*)>1 ORDER BY sum(pg_relation_size(idx)) DESC;
6. Find out tables size
Checkup on table size is also important so we can know which table is largest and we can take some action such as: archiving or truncating the table.
SELECT relname as "Table", pg_size_pretty(pg_relation_size(relid)) As " Table Size", pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "Index Size" FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
Actually there’re more ways help to monitor the performance of the database. However those queries I find most useful for my case. So hope it’s also helpful to you.