-
-
Save jzavisek/68feadde2c02995d098e58f2425f82b8 to your computer and use it in GitHub Desktop.
Postgres diagnostics
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- List all existing indexes and include some useful info about them (incl. the index's definition) | |
SELECT | |
schemaname AS schemaname, | |
t.relname AS tablename, | |
ix.relname AS indexname, | |
regexp_replace(pg_get_indexdef(i.indexrelid), '^[^\(]*\((.*)\)$', '\1') AS columns, | |
regexp_replace(pg_get_indexdef(i.indexrelid), '.* USING ([^ ]*) \(.*', '\1') AS algorithm, | |
indisunique AS UNIQUE, | |
indisprimary AS PRIMARY, indisvalid AS valid, | |
indexprs::text, | |
indpred::text, | |
pg_get_indexdef(i.indexrelid) AS definition | |
FROM | |
pg_index i | |
INNER JOIN pg_class t ON t.oid = i.indrelid | |
INNER JOIN pg_class ix ON ix.oid = i.indexrelid | |
LEFT JOIN pg_stat_user_indexes ui ON ui.indexrelid = i.indexrelid | |
WHERE | |
schemaname IS NOT NULL | |
ORDER BY | |
schemaname ASC, | |
tablename ASC, | |
indexname ASC; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Enable the auto_explain plugin. Useful for doing `explain analyze` on PG functions | |
-- which consist of many nested statements you need to know which of those statements | |
-- are taking long and why. | |
-- Sadly, this plugin is not available on Heroku Postgres. You should migrate to RDS. :) | |
-- AWS RDS | |
LOAD '$libdir/plugins/auto_explain'; | |
-- General PG installation | |
LOAD 'auto_explain'; | |
SET auto_explain.log_format=text; | |
SET auto_explain.log_analyze = true; | |
SET auto_explain.log_nested_statements = true; | |
SET auto_explain.log_timing = true; | |
-- Set this to 0 to enable analysis on all statements | |
SET auto_explain.log_min_duration = 500; | |
-- Optionally send all the auto_explain messages to your preferred GUI client | |
-- Otherwise the messages might only appear in the db logs | |
SET client_min_messages = log; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- See which queries are being blocked by which other query. | |
-- Note that seeing SOMETHING here does not necessarily mean a problem, but the results | |
-- should change quickly. Only when a statement hangs for too long it is a cause for concern. | |
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, | |
blocked_activity.xact_start AS query_start | |
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 | |
ORDER BY | |
blocking_pid; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Lists indexes which are too big compared to the actual table size (I think). | |
-- Not sure I fully understand this one, use with caution. | |
WITH btree_index_atts AS ( | |
SELECT | |
nspname, | |
relname, | |
reltuples, | |
relpages, | |
indrelid, | |
relam, | |
regexp_split_to_table(indkey::text, ' ')::smallint AS attnum, | |
indexrelid AS index_oid | |
FROM | |
pg_index | |
JOIN pg_class ON pg_class.oid = pg_index.indexrelid | |
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace | |
JOIN pg_am ON pg_class.relam = pg_am.oid | |
WHERE | |
pg_am.amname = 'btree' | |
), | |
index_item_sizes AS ( | |
SELECT | |
i.nspname, | |
i.relname, | |
i.reltuples, | |
i.relpages, | |
i.relam, | |
(quote_ident(s.schemaname) || '.' || quote_ident(s.tablename))::regclass AS starelid, | |
a.attrelid AS table_oid, | |
index_oid, | |
current_setting('block_size')::numeric AS bs, | |
/* MAXALIGN: 4 on 32bits, 8 on 64bits (and mingw32 ?) */ | |
CASE WHEN version() ~ 'mingw32' | |
OR version() ~ '64-bit' THEN | |
8 | |
ELSE | |
4 | |
END AS maxalign, | |
24 AS pagehdr, | |
/* per tuple header: add index_attribute_bm if some cols are null-able */ | |
CASE WHEN max(coalesce(s.null_frac, | |
0)) = 0 THEN | |
2 | |
ELSE | |
6 | |
END AS index_tuple_hdr, | |
/* data len: we remove null values save space using it fractionnal part from stats */ | |
sum((1 - coalesce(s.null_frac, | |
0)) * coalesce(s.avg_width, | |
2048)) AS nulldatawidth | |
FROM | |
pg_attribute AS a | |
JOIN pg_stats AS s ON (quote_ident(s.schemaname) || '.' || quote_ident(s.tablename))::regclass = a.attrelid | |
AND s.attname = a.attname | |
JOIN btree_index_atts AS i ON i.indrelid = a.attrelid | |
AND a.attnum = i.attnum | |
WHERE | |
a.attnum > 0 | |
GROUP BY | |
1, | |
2, | |
3, | |
4, | |
5, | |
6, | |
7, | |
8, | |
9 | |
), | |
index_aligned AS ( | |
SELECT | |
maxalign, | |
bs, | |
nspname, | |
relname AS index_name, | |
reltuples, | |
relpages, | |
relam, | |
table_oid, | |
index_oid, | |
(2 + maxalign - CASE | |
/* Add padding to the index tuple header to align on MAXALIGN */ | |
WHEN index_tuple_hdr % maxalign = 0 THEN | |
maxalign | |
ELSE | |
index_tuple_hdr % maxalign | |
END + nulldatawidth + maxalign - CASE | |
/* Add padding to the data to align on MAXALIGN */ | |
WHEN nulldatawidth::integer % maxalign = 0 THEN | |
maxalign | |
ELSE | |
nulldatawidth::integer % maxalign | |
END)::numeric AS nulldatahdrwidth, | |
pagehdr | |
FROM | |
index_item_sizes AS s1 | |
), | |
otta_calc AS ( | |
SELECT | |
bs, | |
nspname, | |
table_oid, | |
index_oid, | |
index_name, | |
relpages, | |
coalesce(ceil((reltuples * (4 + nulldatahdrwidth)) / (bs - pagehdr::float)) + CASE WHEN am.amname IN('hash', 'btree') THEN | |
1 | |
ELSE | |
0 | |
END, | |
0 | |
/* btree and hash have a metadata reserved block */ | |
) AS otta | |
FROM | |
index_aligned AS s2 | |
LEFT JOIN pg_am am ON s2.relam = am.oid | |
), | |
raw_bloat AS ( | |
SELECT | |
nspname, | |
c.relname AS table_name, | |
index_name, | |
bs * (sub.relpages)::bigint AS totalbytes, | |
CASE WHEN sub.relpages <= otta THEN | |
0 | |
ELSE | |
bs * (sub.relpages - otta)::bigint | |
END AS wastedbytes, | |
CASE WHEN sub.relpages <= otta THEN | |
0 | |
ELSE | |
bs * (sub.relpages - otta)::bigint * 100 / (bs * (sub.relpages)::bigint) | |
END AS realbloat, | |
pg_relation_size(sub.table_oid) AS table_bytes, | |
stat.idx_scan AS index_scans, | |
stat.indexrelid | |
FROM | |
otta_calc AS sub | |
JOIN pg_class AS c ON c.oid = sub.table_oid | |
JOIN pg_stat_user_indexes AS stat ON sub.index_oid = stat.indexrelid | |
) | |
SELECT | |
nspname AS schemaname, | |
table_name AS tablename, | |
index_name AS indexname, pg_size_pretty(wastedbytes) AS bloat_bytes, | |
pg_size_pretty(totalbytes) AS index_bytes, | |
pg_get_indexdef(rb.indexrelid) AS definition, | |
indisprimary AS primary | |
FROM | |
raw_bloat rb | |
INNER JOIN pg_index i ON i.indexrelid = rb.indexrelid | |
WHERE | |
wastedbytes >= 100000000 | |
ORDER BY | |
wastedbytes DESC, indexname; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- See on which tables PG is doing a lot of sequential scans. On small tables | |
-- a seq scan is more efficient than an index scan so always think if adding | |
-- an index would really make sense. | |
-- Sadly, it does not show which fields are "hot", only the tables names. | |
SELECT | |
relname as table, | |
pg_size_pretty(pg_relation_size(relid::regclass)) AS size, | |
seq_scan as sequential_scans, | |
idx_scan as index_scans, | |
seq_scan - idx_scan AS difference, | |
CASE WHEN seq_scan - idx_scan > 0 THEN | |
'Missing Index?' | |
ELSE | |
'OK' | |
END AS status | |
FROM | |
pg_stat_all_tables | |
WHERE | |
schemaname = 'public' | |
AND pg_relation_size(relid::regclass) > 80000 | |
ORDER BY | |
difference DESC; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- See which indexes are not being used or are being used seldomly. | |
-- Change the `idx_scan` query filter to your liking - for heavy workloads, | |
-- even an index with several thousand hits could be potentially "useless" | |
-- if other indexes have hit rates in millions. | |
SELECT | |
schemaname AS schemaname, | |
relname AS tablename, | |
indexrelname AS indexname, | |
pg_size_pretty(pg_relation_size(i.indexrelid)) AS size, | |
idx_scan AS indexscans | |
FROM | |
pg_stat_user_indexes ui | |
INNER JOIN pg_index i ON ui.indexrelid = i.indexrelid | |
WHERE | |
NOT indisunique | |
AND idx_scan <= 50 | |
ORDER BY | |
pg_relation_size(i.indexrelid) DESC, | |
relname ASC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment