Unused Indexes - PostgreSQL Database
2
votes
1
answer
1002
views
I have used attached query to check unused indexes in my EDB9.5 DB. It's showing attached indexes are the unused indexes. Is it safe to drop below unused indexes?
SELECT
idstat.relname AS TABLE_NAME,
indexrelname AS index_name,
idstat.idx_scan AS index_scans_count,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
tabstat.idx_scan AS table_reads_index_count,
tabstat.seq_scan AS table_reads_seq_count,
tabstat.seq_scan + tabstat.idx_scan AS table_reads_count,
n_tup_upd + n_tup_ins + n_tup_del AS table_writes_count,
pg_size_pretty(pg_relation_size(idstat.relid)) AS table_size
FROM
pg_stat_user_indexes AS idstat
JOIN
pg_indexes
ON
indexrelname = indexname
AND
idstat.schemaname = pg_indexes.schemaname
JOIN
pg_stat_user_tables AS tabstat
ON
idstat.relid = tabstat.relid
WHERE
indexdef !~* 'unique'
and idstat.relname='cwt_act'
ORDER BY
idstat.idx_scan DESC,
pg_relation_size(indexrelid) DESC;
Please suggest!
Asked by NEI
(31 rep)
Jan 13, 2021, 11:01 AM
Last activity: Jan 14, 2021, 02:25 PM
Last activity: Jan 14, 2021, 02:25 PM