-
-
Save take-five/81a5f3d4f29453aa9daf to your computer and use it in GitHub Desktop.
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
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment