Index analysis
The Indexes tab identifies index problems and suggests concrete fixes.
Unused indexes
Indexes that appear large enough to matter but are rarely scanned. Primary and constraint-backed indexes are excluded from drop-oriented guidance.
Duplicate indexes
Indexes that cover the same columns in the same order on the same table.
Missing FK indexes
Foreign key columns without a supporting index. Each finding includes a ready-to-copy CREATE INDEX CONCURRENTLY statement.
Sequential scan vs index scan
Tables with high sequential scan ratios, with context:
| Situation | Meaning |
|---|---|
| missing | No valid indexes at all — add an index |
| pk_only | Only primary key indexed — add index on filter columns |
| sparse | 1-2 non-PK indexes — likely missing one |
| rich | 3+ non-PK indexes, planner bypassing them — check selectivity, not more indexes |
Severity is driven by how often the table is scanned and how much data each scan touches. The UI includes an approximate overhead estimate so you can prioritize tables that are likely to matter.
When planner cost settings look mismatched for SSD-backed storage, pocketPG may show a cost-setting advisory instead of recommending another index.
Smart index suggestions
For higher-severity tables, pocketPG looks at query patterns from pg_stat_statements to suggest likely index columns.
The internal column-ranking weights are not documented publicly. In general, equality predicates are favored before range predicates, and generated SQL is only shown when the app can produce a concrete recommendation with enough confidence.
Index bloat
Estimated index bloat is shown with REINDEX CONCURRENTLY actions when the evidence supports a safe maintenance recommendation.
Caching
Index data is short-lived cached because it is relatively expensive to compute and usually does not change second-by-second.