PostgreSQL Index Usage Analysis

Question

Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the "profiler" tool for SQLServer, but I'm not aware of a similar tool included with Postgres.

Answer 1

This should help: Pratical Query Analysis Cheers!

Answer 2

On the determine missing indexes approach....Nope. But there's some plans to make this easier in future release, like pseudo-indexes and machine readable EXPLAIN.

Currently, you'll need to EXPLAIN ANALYZE poor performing queries and then manually determine the best route. Some log analyzers like pgFouine can help in determination of the queries.

As far as unused index, you can use something like the following to help identify them:

select * from pg_stat_all_indexes where schemaname <> 'pg_catalog';

This will help identify tuples read,scanned,fetched.

Answer 3

Check the statistics, pg_stat_user_tables and pg_stat_user_indexes are the ones to start with.

http://www.postgresql.org/docs/current/static/monitoring-stats.html#MONITORING-STATS-SETUP

Answer 4

There are multiple links to scripts that will help you find unused indexes at the PostgreSQL wiki. The basic technique is to look at pg_stat_user_indexes and look for ones where idx_scan, the count of how many times that index has been used to answer queries, is zero, or at least very low. If the application has changed and a formerly used index probably isn't now, you sometimes have to run pg_stat_reset() to get all the statistics back to 0 and then collect new data; you might save the current values for everything and compute a delta instead to figure that out.

There isn't any good tool available yet to suggest missing indexes. One approach is to log the queries you're running and analyze which ones are taking a long time to run using a query log analysis tool like pgFouine or pqa. See Logging Difficult Queries for more info.

The other approach is to look at pg_stat_user_tables and look for tables that have large numbers of sequential scans against them, where seq_tup_fetch is large. When an index is used the idx_fetch_tup count is increased instead. That can clue you into when a table is not indexed well enough to answer queries against it. Actually figuring out which columns you should then index on? That usually leads back to the query log analysis stuff again.