Today everything is about maintenace of the postgres database so, searching and building scripts. Don't try to re-invent the wheel...
cheers
/****http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html***************/
/**************unused index******************/
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
( case when total_writes = 0 then 0 else writes::NUMERIC / ( total_writes + 1 )end ) > 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;
/******************Table scan summary***********************************************/
SELECT
x1.table_in_trouble,
pg_relation_size(x1.table_in_trouble) AS sz_n_byts,
x1.seq_scan, x1.idx_scan,
CASE
WHEN pg_relation_size(x1.table_in_trouble) > 500000000
THEN 'Exceeds 500 megs, too large to count in a view. For a count, count individually'::text
ELSE count(x1.table_in_trouble)::text
END AS tbl_rec_count,
x1.priority
FROM
(
SELECT
(schemaname::text || '.'::text) || relname::text AS table_in_trouble,
seq_scan,
idx_scan,
CASE
WHEN (seq_scan - idx_scan) < 500 THEN 'Minor Problem'::text
WHEN (seq_scan - idx_scan) >= 500 AND (seq_scan - idx_scan) < 2500 THEN 'Major Problem'::text
WHEN (seq_scan - idx_scan) >= 2500 THEN 'Extreme Problem'::text
ELSE NULL::text
END AS priority
FROM
pg_stat_all_tables
WHERE
seq_scan > idx_scan
AND schemaname != 'pg_catalog'::name
AND seq_scan > 100) x1
GROUP BY
x1.table_in_trouble,
x1.seq_scan,
x1.idx_scan,
x1.priority
ORDER BY
x1.priority DESC,
x1.seq_scan
;
/********************Check Query Plan*********************/
explain ( analyze, BUFFERS)
select * from ?
SELECT
pg_class.relname,
pg_size_pretty(pg_class.reltuples::bigint) AS rows_in_bytes,
pg_class.reltuples AS num_rows,
count(indexname) AS number_of_indexes,
CASE WHEN x.is_unique = 1 THEN 'Y'
ELSE 'N'
END AS UNIQUE,
SUM(case WHEN number_of_columns = 1 THEN 1
ELSE 0
END) AS single_column,
SUM(case WHEN number_of_columns IS NULL THEN 0
WHEN number_of_columns = 1 THEN 0
ELSE 1
END) AS multi_column
FROM pg_namespace
LEFT OUTER JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
LEFT OUTER JOIN
(SELECT indrelid,
max(CAST(indisunique AS integer)) AS is_unique
FROM pg_index
GROUP BY indrelid) x
ON pg_class.oid = x.indrelid
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts
AS number_of_columns FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid )
AS foo
ON pg_class.relname = foo.ctablename
WHERE
pg_namespace.nspname='SchemaNameHere'
AND pg_class.relkind = 'r'
GROUP BY pg_class.relname, pg_class.reltuples, x.is_unique
ORDER BY 2;
/****Postgres index Stats Summary******/
SELECT
distinct
t.tablename,
indexname,
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(t.schemaname::TEXT||'.'||quote_ident((t.tablename))::text)) AS table_size,
pg_size_pretty(pg_relation_size(t.schemaname||'.'||quote_ident(indexrelname)::text)) AS index_size,
CASE WHEN indisunique THEN 'Y'
ELSE 'N'
END AS UNIQUE,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
LEFT OUTER JOIN
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname='SchemaNameHere'
ORDER BY 1,2;
