pg_debug.sql

-- currently running queries (youngest to oldest)

SELECT pid, state, age(clock_timestamp(), query_start), substring(trim(regexp_replace(query, '\s+', ' ', 'g')) from 1 for 200)
FROM pg_stat_activity 
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;



-- x blocking y

SELECT
    activity.pid,
    activity.usename,
    substring(trim(regexp_replace(activity.query, '\s+', ' ', 'g')) from 1 for 200),
    blocking.pid AS blocking_id,
    substring(trim(regexp_replace(blocking.query, '\s+', ' ', 'g')) from 1 for 200) AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));





-- who's locking what?
select 
	locktype, relation::regclass, mode,
	pid, 
	substring(trim(regexp_replace(query, '\s+', ' ', 'g')) from 1 for 100) as query
from pg_locks 
join pg_stat_activity using (pid)
order by relation;




-- index sizes
SELECT i.relname "Table Name",indexrelname "Index Name",
 pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
 pg_size_pretty(pg_indexes_size(relid)) as "Total Size of all Indexes",
 pg_size_pretty(pg_relation_size(relid)) as "Table Size",
 pg_size_pretty(pg_relation_size(indexrelid)) "Index Size",
 reltuples::bigint "Estimated table row count"
 FROM pg_stat_all_indexes i JOIN pg_class c ON i.relid=c.oid
 and i.relname not like 'pg_%'
 order by pg_total_relation_size(relid) desc;

 
 -- slow queries
 SELECT substring(trim(regexp_replace(query, '\s+', ' ', 'g')) from 1 for 200) AS query, calls,
     round(total_exec_time::numeric, 2) AS total_time,
     round(mean_exec_time::numeric, 2) AS mean_time,
     round((100 * total_exec_time / sum(total_exec_time) 
				 OVER ())::numeric, 2) AS percentage
FROM  pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 50;