postgresql-drop-unused-indexes.sh

postgresql-drop-unused-indexes.sh

#!/bin/bash

# Written by demofly for Pg 9.4
#
# WARNING: if you ran pg_stat_reset() last month, don't use this script !!!
#
# Get unused indexes and kill it with fire!

DB=postgres

echo "SELECT
    c.schemaname || '.' || c.indexrelname AS index
FROM
    pg_index AS b
    JOIN pg_class AS a ON (a.oid = b.indrelid)
    JOIN pg_stat_all_indexes AS c ON (c.indexrelid = b.indexrelid)
    JOIN pg_class AS i ON (i.oid = b.indexrelid)
WHERE c.idx_scan=0
    AND c.schemaname NOT LIKE ('pg%')
    and a.relkind = 'r'
    AND b.indisunique != true
    AND b.indisprimary != true
ORDER BY
    index;
" | psql "${DB}" -A | tail -n+2 | head -n-1 | while read IDX
do
    SQL="DROP INDEX CONCURRENTLY ${IDX}"
    echo "${SQL}"
    echo "${SQL}" | psql "${DB}"
done