Monday, July 13, 2015

[PostgreSQL] List index usage

Update: This query is much cooler Index size/usage statistics

--

In psql console, connect to the database you want to check its indexes usage. Then execute this query.
# SELECT * FROM pg_stat_user_indexes ORDER BY idx_scan DESC;
If your database is manage by Rails's schema, you could filter out auto generated public key indexes like so
# SELECT * FROM pg_stat_user_indexes WHERE indexrelname NOT LIKE '%_pkey' ORDER BY idx_scan DESC; 

Collectd PostgreSQL Plugin

I couldn't find this link when searching with google https://www.collectd.org/documentation/manpages/collectd.conf.html#plugin-postgresql