PostgreSQL internals SQL collection

psql_internals_cheatsheet.sql

# long running queries:
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';

## active queries and killing them:
SELECT * FROM pg_stat_activity WHERE state = 'active'; #So you can identify the PID of the hanging query you want to terminate, run this:
SELECT pg_cancel_backend(PID); #This query might take a while to kill the query, so if you want to kill it the hard way, run this instead:
SELECT pg_terminate_backend(PID);

# waiting events:
SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event is NOT NULL;

SELECT a.datname,
         l.relation::regclass,
         l.transactionid,
         l.mode,
         l.GRANTED,
         a.usename,
         a.query,
         a.query_start,
         age(now(), a.query_start) AS "age",
         a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;

# blocked activity:

SELECT blocked_locks.pid     AS blocked_pid,
	 blocked_activity.usename  AS blocked_user,
	 blocking_locks.pid     AS blocking_pid,
	 blocking_activity.usename AS blocking_user,
	 blocked_activity.query    AS blocked_statement,
	 blocking_activity.query   AS current_statement_in_blocking_process
FROM  pg_catalog.pg_locks         blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks         blocking_locks 
	ON blocking_locks.locktype = blocked_locks.locktype
	AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
	AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
	AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
	AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
	AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
	AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
	AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
	AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
	AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
	AND blocking_locks.pid != blocked_locks.pid

JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

# transaction age:
## on db level:
with datage as (
select datname, age(datfrozenxid) as xid_age,
    round(pg_database_size(oid)/(128*1024::numeric),1) as gb_size
    from pg_database 
    where datname not in ('rdsadmin') -- no perms to examine this one (AWS)
),
av_max_age as (
    select setting::numeric as max_age from pg_settings where name = 'autovacuum_freeze_max_age'
),
wrap_pct AS (
select datname, xid_age,
    round(xid_age*100::numeric/max_age, 1) as av_wrap_pct,
    round(xid_age*100::numeric/2200000000, 1) as shutdown_pct,
    gb_size
from datage cross join av_max_age
)
SELECT wrap_pct.*
FROM wrap_pct
WHERE ((av_wrap_pct >= 75 or shutdown_pct > 50
    and gb_size > 1))
    or (av_wrap_pct > 100 or shutdown_pct > 80)
order by xid_age desc;


## on table level:
with relage as (
select relname, age(relfrozenxid) as xid_age,
    round((relpages/128::numeric),1) as mb_size
    from pg_class
where relkind IN ('r', 't')
),
av_max_age as (
    select setting::numeric as max_age from pg_settings where name = 'autovacuum_freeze_max_age'
),
wrap_pct AS (
select relname, xid_age,
    round(xid_age*100::numeric/max_age, 1) as av_wrap_pct,
    round(xid_age*100::numeric/2200000000, 1) as shutdown_pct,
    mb_size
from relage cross join av_max_age
)
select wrap_pct.*
from wrap_pct
where ((av_wrap_pct >= 75
    or shutdown_pct >= 50)
    and mb_size > 1000)
    or
    (av_wrap_pct > 100
    or shutdown_pct > 80)
order by xid_age desc;


# vacuum / analyze

-- Show last autovacuum by table size descending.
select
    pg_class.relname,
    pg_namespace.nspname,
    pg_size_pretty(pg_total_relation_size(pg_namespace.nspname::text || '.' || pg_class.relname::text)),
    pg_stat_all_tables.last_autovacuum,
    pg_relation_size(pg_namespace.nspname::text || '.' || pg_class.relname::text)
from
    pg_class
        join pg_namespace
            on pg_class.relnamespace = pg_namespace.oid
        join pg_stat_all_tables
            on (pg_class.relname = pg_stat_all_tables.relname AND pg_namespace.nspname = pg_stat_all_tables.schemaname)
where
    pg_namespace.nspname not in ('pg_toast')
order by
    5 desc
;

    
-- Show last autovacuum by table size descending.
select
    pg_class.relname,
    pg_namespace.nspname,
    pg_size_pretty(pg_total_relation_size(pg_namespace.nspname::text || '.' || pg_class.relname::text)),
    pg_stat_all_tables.last_autovacuum,
    pg_stat_all_tables.last_autoanalyze,
    pg_relation_size(pg_namespace.nspname::text || '.' || pg_class.relname::text)
from
    pg_class
        join pg_namespace
            on pg_class.relnamespace = pg_namespace.oid
        join pg_stat_all_tables
            on (pg_class.relname = pg_stat_all_tables.relname AND pg_namespace.nspname = pg_stat_all_tables.schemaname)
where
    pg_namespace.nspname not in ('pg_toast')
order by
    5 desc
;

# index/indicies:
## duplicates:
\o /tmp/duplicate-indexes.txt

-- check for exact matches
SELECT indrelid::regclass
     , array_agg(indexrelid::regclass)
  FROM pg_index
 GROUP BY indrelid
     , indkey
HAVING COUNT(*) > 1;

-- check for matches on only the first column of the index
-- requires some human eyeballing to verify
SELECT indrelid::regclass
     , array_agg(indexrelid::regclass)
  FROM pg_index
 GROUP BY indrelid
     , indkey[0]
HAVING COUNT(*) > 1;

\o

## fuzzy search for duplicates:
-- check for containment
-- i.e. index A contains index B
-- and both share the same first column
-- but they are NOT identical

WITH index_cols_ord as (
    SELECT attrelid, attnum, attname
    FROM pg_attribute
        JOIN pg_index ON indexrelid = attrelid
    WHERE indkey[0] > 0
    ORDER BY attrelid, attnum
),
index_col_list AS (
    SELECT attrelid,
        array_agg(attname) as cols
    FROM index_cols_ord
    GROUP BY attrelid
),
dup_natts AS (
SELECT indrelid, indexrelid
FROM pg_index as ind
WHERE EXISTS ( SELECT 1
    FROM pg_index as ind2
    WHERE ind.indrelid = ind2.indrelid
    AND ( ind.indkey @> ind2.indkey
     OR ind.indkey <@ ind2.indkey )
    AND ind.indkey[0] = ind2.indkey[0]
    AND ind.indkey <> ind2.indkey
    AND ind.indexrelid <> ind2.indexrelid
) )
SELECT userdex.schemaname as schema_name,
    userdex.relname as table_name,
    userdex.indexrelname as index_name,
    array_to_string(cols, ', ') as index_cols,
    indexdef,
    idx_scan as index_scans
FROM pg_stat_user_indexes as userdex
    JOIN index_col_list ON index_col_list.attrelid = userdex.indexrelid
    JOIN dup_natts ON userdex.indexrelid = dup_natts.indexrelid
    JOIN pg_indexes ON userdex.schemaname = pg_indexes.schemaname
        AND userdex.indexrelname = pg_indexes.indexname
ORDER BY userdex.schemaname, userdex.relname, cols, userdex.indexrelname;

## unused indicies:
WITH table_scans as (
    SELECT relid,
        tables.idx_scan + tables.seq_scan as all_scans,
        ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as writes,
                pg_relation_size(relid) as table_size
        FROM pg_stat_user_tables as tables
),
all_writes as (
    SELECT sum(writes) as total_writes
    FROM table_scans
),
indexes as (
    SELECT idx_stat.relid, idx_stat.indexrelid,
        idx_stat.schemaname, idx_stat.relname as tablename,
        idx_stat.indexrelname as indexname,
        idx_stat.idx_scan,
        pg_relation_size(idx_stat.indexrelid) as index_bytes,
        indexdef ~* 'USING btree' AS idx_is_btree
    FROM pg_stat_user_indexes as idx_stat
        JOIN pg_index
            USING (indexrelid)
        JOIN pg_indexes as indexes
            ON idx_stat.schemaname = indexes.schemaname
                AND idx_stat.relname = indexes.tablename
                AND idx_stat.indexrelname = indexes.indexname
    WHERE pg_index.indisunique = FALSE
),
index_ratios AS (
SELECT schemaname, tablename, indexname,
    idx_scan, all_scans,
    round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC
        ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct,
    writes,
    round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2)
        as scans_per_write,
    pg_size_pretty(index_bytes) as index_size,
    pg_size_pretty(table_size) as table_size,
    idx_is_btree, index_bytes
    FROM indexes
    JOIN table_scans
    USING (relid)
),
index_groups AS (
SELECT 'Never Used Indexes' as reason, *, 1 as grp
FROM index_ratios
WHERE
    idx_scan = 0
    and idx_is_btree
UNION ALL
SELECT 'Low Scans, High Writes' as reason, *, 2 as grp
FROM index_ratios
WHERE
    scans_per_write <= 1
    and index_scan_pct < 10
    and idx_scan > 0
    and writes > 100
    and idx_is_btree
UNION ALL
SELECT 'Seldom Used Large Indexes' as reason, *, 3 as grp
FROM index_ratios
WHERE
    index_scan_pct < 5
    and scans_per_write > 1
    and idx_scan > 0
    and idx_is_btree
    and index_bytes > 100000000
UNION ALL
SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp 
FROM index_ratios, all_writes
WHERE
    ( writes::NUMERIC / ( total_writes + 1 ) ) > 0.02
    AND NOT idx_is_btree
    AND index_bytes > 100000000
ORDER BY grp, index_bytes DESC )
SELECT reason, schemaname, tablename, indexname,
    index_scan_pct, scans_per_write, index_size, table_size
FROM index_groups;

## needed indicies:
WITH 
index_usage AS (
    SELECT  sut.relid,
            current_database() AS database,
            sut.schemaname::text as schema_name, 
            sut.relname::text AS table_name,
            sut.seq_scan as table_scans,
            sut.idx_scan as index_scans,
            pg_total_relation_size(relid) as table_bytes,
            round((sut.n_tup_ins + sut.n_tup_del + sut.n_tup_upd + sut.n_tup_hot_upd) / 
                (seq_tup_read::NUMERIC + 2), 2) as writes_per_scan
    FROM pg_stat_user_tables sut
),
index_counts AS (
    SELECT sut.relid,
        count(*) as index_count
    FROM pg_stat_user_tables sut LEFT OUTER JOIN pg_indexes
    ON sut.schemaname = pg_indexes.schemaname AND
        sut.relname = pg_indexes.tablename
    GROUP BY relid
),
too_many_tablescans AS (
    SELECT 'many table scans'::TEXT as reason, 
        database, schema_name, table_name,
        table_scans, pg_size_pretty(table_bytes) as table_size,
        writes_per_scan, index_count, table_bytes
    FROM index_usage JOIN index_counts USING ( relid )
    WHERE table_scans > 1000
        AND table_scans > ( index_scans * 2 )
        AND table_bytes > 32000000
        AND writes_per_scan < ( 1.0 )
    ORDER BY table_scans DESC
),
scans_no_index AS (
    SELECT 'scans, few indexes'::TEXT as reason,
        database, schema_name, table_name,
        table_scans, pg_size_pretty(table_bytes) as table_size,
        writes_per_scan, index_count, table_bytes
    FROM index_usage JOIN index_counts USING ( relid )
    WHERE table_scans > 100
        AND table_scans > ( index_scans )
        AND index_count < 2
        AND table_bytes > 32000000   
        AND writes_per_scan < ( 1.0 )
    ORDER BY table_scans DESC
),
big_tables_with_scans AS (
    SELECT 'big table scans'::TEXT as reason,
        database, schema_name, table_name,
        table_scans, pg_size_pretty(table_bytes) as table_size,
        writes_per_scan, index_count, table_bytes
    FROM index_usage JOIN index_counts USING ( relid )
    WHERE table_scans > 100
        AND table_scans > ( index_scans / 10 )
        AND table_bytes > 1000000000  
        AND writes_per_scan < ( 1.0 )
    ORDER BY table_bytes DESC
),
scans_no_writes AS (
    SELECT 'scans, no writes'::TEXT as reason,
        database, schema_name, table_name,
        table_scans, pg_size_pretty(table_bytes) as table_size,
        writes_per_scan, index_count, table_bytes
    FROM index_usage JOIN index_counts USING ( relid )
    WHERE table_scans > 100
        AND table_scans > ( index_scans / 4 )
        AND table_bytes > 32000000   
        AND writes_per_scan < ( 0.1 )
    ORDER BY writes_per_scan ASC
)
SELECT reason, database, schema_name, table_name, table_scans, 
    table_size, writes_per_scan, index_count
FROM too_many_tablescans
UNION ALL
SELECT reason, database, schema_name, table_name, table_scans, 
    table_size, writes_per_scan, index_count
FROM scans_no_index
UNION ALL
SELECT reason, database, schema_name, table_name, table_scans, 
    table_size, writes_per_scan, index_count
FROM big_tables_with_scans
UNION ALL
SELECT reason, database, schema_name, table_name, table_scans, 
    table_size, writes_per_scan, index_count
FROM scans_no_writes;




# locks:
## logging (logs if a lock takes longer than deadlock_timeout duration setting/default - 1s in postgres.conf)
set log_lock_waits = on;
 
## all locks:

SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid;
	
## specific (ie. more useful):
SELECT usename, 
	locktype, 
	mode, 
	database, 	
	(state_change - query_start) AS duration,
	query_start,
	state_change,
	granted, 
	wait_event_type,
	wait_event
FROM pg_locks pl LEFT JOIN pg_stat_activity psa
ON pl.pid = psa.pid;

## locks from prepared statements only:
SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
    ON pl.virtualtransaction = '-1/' || ppx.transaction;