PostgreSQL index advisor
Look at every query that touches a table, not one query at a time. Propose the smallest set of indexes that serves them, drop the ones another index covers or nothing reads, and confirm with the planner that each new index would be used. dbtempo’s index advisor does this for each table from pg_stat_statements.
It reads the whole table’s workload
pg_stat_statements gives calls and time for each normalized query. The advisor groups the queries by table, reads the indexes already on it, and proposes one index set for the table, so one composite index can serve several access patterns instead of one index being added per query.
It says what to drop
- Redundant: another index already covers it, and it is not a unique index.
- Unused: no index scans recorded against it.
- Removals are never applied unattended. With indexes set to Ask me first, they wait for your approval.
It checks with the planner
Where the HypoPG extension is installed, each candidate is created as a hypothetical index, which exists only for the planner, and the query is planned again. A candidate the planner would ignore is not recommended. Nothing is built on disk during the check.
Beyond indexes
The same scans check PostgreSQL maintenance: dead rows accumulating, stale statistics, cleanup that is blocked, and transaction-ID wraparound. Each finding says when the condition clears, not only when it starts.