Queries to check Postgres query performance

Some queries to check Postgres Performance

Show sessions older than 5 minutes

===================================

SELECT

pid,

now() - pg_stat_activity.query_start AS duration,

query,

state

FROM pg_stat_activity

WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Show mean time per query

========================

select queryid,query as short_query,

round(total_time::numeric,2) as total_time,

calls, round(mean_time::numeric,2) as mean,

round((100 * total_time / sum(total_time::numeric)

OVER ())::numeric, 2) as percentage_overall

from pg_stat_statements order by total_time desc

limit 20;

Sequential scans on tables

===========================

select schemaname, relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan as AVG

from pg_stat_user_tables

where seq_scan > 0

order by seq_tup_read desc;

Fragmentation of tables

=======================

SELECT

relname AS ObjectName

,pg_stat_get_live_tuples(c.oid) AS LiveTuples

,pg_stat_get_dead_tuples(c.oid) AS DeadTuples

FROM pg_class c

where pg_stat_get_live_tuples(c.oid) > 0 or pg_stat_get_dead_tuples(c.oid) > 0

order by pg_stat_get_live_tuples(c.oid),pg_stat_get_dead_tuples(c.oid) desc;

Show status of tables

=====================

select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_all_tables

where relname='TABLE_NAME';

Check Locks:

============

select * from pg_locks;

Check Waits:

============

select pid, wait_event_type, wait_event from pg_stat_activity where wait_event is not null;

Any blocks or locked queries/sessions:

=======================================

SELECT

pl.pid as blocked_pid

,psa.usename as blocked_user

,pl2.pid as blocking_pid

,psa2.usename as blocking_user

,psa.query as blocked_statement

FROM pg_catalog.pg_locks pl

JOIN pg_catalog.pg_stat_activity psa

ON pl.pid = psa.pid

JOIN pg_catalog.pg_locks pl2

JOIN pg_catalog.pg_stat_activity psa2

ON pl2.pid = psa2.pid

ON pl.transactionid = pl2.transactionid

AND pl.pid != pl2.pid

WHERE NOT pl.granted;

Connections into the DB

==========================

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

Connections waiting for a lock

================================

SELECT count(distinct pid) FROM pg_locks WHERE granted = false;