Sample Header Ad - 728x90

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