PostgreSQL Indexes helps review index usage and tuning opportunities for a monitored PostgreSQL server.
What to review
- Index inventory.
- Usage patterns and low-use indexes where available.
- Missing or inefficient index opportunities where available.
- Relationship between indexes and expensive queries.
Workflow
- Open Indexes.
- Review Index Details for inventory, validity, readiness, size, scans, writes, and usage.
- Review Table Maintenance for dead tuples, autovacuum/analyze signals, wraparound risk, and optional bloat estimates.
- Review Health Issues for rows Mini DBA has classified as needing attention.
- Review Missing Indexes for advisory recommendations backed by table statistics and, where available, sampled query evidence.
- Compare with Queries and Activity.
- Test index changes before applying them to production.
Indexes page tabs
Index Details is the index inventory. It shows every visible user index, including health and ready status near the index name, validity, scans, writes, tuple activity, and size. Use this tab when you need to inspect a known index or compare read value against write cost.
Table Maintenance is the table inventory and maintenance view. It intentionally includes healthy rows as well as rows with issues, so the lower detail area can explain the selected table. It shows live/dead tuple counts, vacuum/analyze history, autovacuum signals, wraparound risk, and a Dead Tuple Trend for the last 7 days. When pgstattuple is installed and permitted, Mini DBA adds lightweight pgstattuple_approx dead/free-space estimates for selected candidates.
Health Issues contains only detected index and table-maintenance issues. Use it as the short action list after reviewing the broader inventory.
Missing Indexes contains advisory recommendations. Rows are not automatic DDL instructions; they combine table scan evidence, table size, column selectivity where available, existing index coverage, maintenance context, and sampled query evidence where Query Intelligence has matching history. Use the copy button beside Recommended SQL to copy the suggested statement, then review and test it using your normal change process.
Debug workload buttons
In debug builds, or in an administrator kiosk/debug session where enabled, the Indexes page may show amber debug workload buttons:
- Trigger Missing Index Workload creates a controlled
minidba_debug_indexload_*workload that produces a repeatable missing-index recommendation. - Trigger Vacuum Pressure creates a retained
minidba_debug_vacuumload_*table with dead tuples so table-maintenance health, alerts, and trend history can be tested.
These workloads need a writable non-system schema in the monitored database, preferably minidba_debug. They are validation tools, not production maintenance features.
How to review PostgreSQL indexes
PostgreSQL index tuning should balance read performance against write overhead, storage, and maintenance. The Indexes page helps find indexes that support expensive queries, indexes that appear underused, and places where query patterns may benefit from a new or changed index.
Start from workload evidence. If PostgreSQL Queries shows a high-impact statement, use the Indexes page to check whether the relevant table has indexes that match the WHERE clause, join columns, and ordering. If a table has many indexes, review whether each index is used and whether write-heavy workloads are paying unnecessary maintenance cost.
Be careful with low-use index findings. An index may be critical for a monthly report, a rare emergency workflow, or a constraint-backed access pattern. Before dropping an index, confirm business usage and test in a non-production environment. For new indexes, consider whether a partial, composite, or covering index would solve the query with less overhead than a broad index.
PostgreSQL index FAQ
Why does table maintenance show rows without issues?
Table Maintenance is an inventory plus explanation view. Healthy rows are shown so you can click a table and see its current tuple counts, vacuum/analyze status, optional bloat estimate, and dead tuple trend. Use Health Issues when you only want rows Mini DBA has classified as needing attention.
Should I create every suggested index?
No. Validate suggestions against query plans, write overhead, storage, and production workload patterns.
Why is pgstattuple unavailable?
pgstattuple is optional. A DBA must install the extension in the monitored database and the monitoring login must be permitted to use it. When it is unavailable, Mini DBA still uses normal catalog and statistics views for table-maintenance signals.
Why can too many indexes hurt performance?
Every INSERT, UPDATE, and DELETE may need to maintain indexes. Extra indexes can slow writes and increase storage and vacuum work.
What evidence should I keep before changing indexes?
Record the query, table, current indexes, runtime, waits, and expected benefit. This makes rollback and before-and-after comparison easier.