检查PostgreSQL索引使用的有效性

滥用索引的危害：

0，降低数据的insert、update和delete速度；

1，增加规划器规划SQL查询的难度，使规划器运行的时间变长，导致整体的查询时间变长；

2，使VACUUM时间变长；

3，增加备份和恢复的时间。

准备工作：

首先必须打开PostgreSQL的统计收集器，对于PostgreSQL 8.3.x来说需要在postgresql.conf里把下面两项打开：

track_activities = on
track_counts = on

然后执行以下命令清理统计收集器的内容：

select pg_stat_reset();

得到 结果：

等待系统运行一段时间之后（数天、数周），执行以下语句：

PostgreSQL 8.3.x版本：

SELECT idstat.relname AS table_name,
indexrelname AS index_name,
idstat.idx_scan AS times_used,
pg_size_pretty(pg_relation_size(idstat.relname)) AS table_size, pg_size_pretty(pg_relation_size(indexrelname)) AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes,
indexdef AS definition
FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE idstat.idx_scan < 200
AND indexdef !~* 'unique'
ORDER BY idstat.relname, indexrelname;

PostgreSQL 8.4.x版本：

SELECT idstat.relname AS table_name,
indexrelname AS index_name,
idstat.idx_scan AS times_used,
idstat.relname AS table_size, indexrelname AS index_size,
n_tup_upd + n_tup_ins + n_tup_del as num_writes,
indexdef AS definition
FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname
JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
WHERE idstat.idx_scan < 200
AND indexdef !~* 'unique'
ORDER BY idstat.relname, indexrelname;

如何处理 ：

drop index ...

在删除大量索引之后，建议reindex和vacuum。