Skip to content

Instantly share code, notes, and snippets.

@azet
Last active March 5, 2021 14:57
Show Gist options
  • Save azet/f4520dc47653a38e1ed2565b8a5e4fcb to your computer and use it in GitHub Desktop.
Save azet/f4520dc47653a38e1ed2565b8a5e4fcb to your computer and use it in GitHub Desktop.
PostgreSQL internals SQL collection
# 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment