Index analysis
The Indexes tab identifies index problems and suggests concrete fixes.
Summary cards
At the top of the page, pocketPG shows a compact summary of:
- Index hit ratio — how often index reads are served from shared buffers instead of disk
- Index bloat — aggregate reclaimable space estimate across the indexes shown on the page
These cards are meant for fast triage. The detailed sections below are the source of truth.
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
pocketPG has a workload-driven index recommendation engine that analyzes query patterns from pg_stat_statements and existing index inventory to suggest concrete index designs.
When pg_stat_statements is available, the engine processes top queries by total execution time, extracts filter predicates, join conditions, and sort clauses, then generates ranked recommendations across multiple index types:
| Method | When recommended |
|---|---|
| B-tree (single/composite) | Equality, range, and sort patterns with high workload share |
| Partial B-tree | Soft-delete/archive patterns (IS NULL predicates on active subset) |
| Covering B-tree | SELECT-column-based INCLUDE clauses for index-only scans |
| Expression B-tree | Case-insensitive LIKE/ILIKE patterns (LOWER, ILIKE) |
| GIN — JSONB | JSON containment (@>, ?) and path-access operators |
| GIN — Full-text search | @@ tsvector operators |
| GIN — Array | Array overlap (&&, @>) operators |
| BRIN | Very large append-only tables with time-like range columns |
| Hash | Equality-only lookups on large columns (rare — most workloads prefer B-tree) |
Recommendations are ranked by a composite score that considers workload impact, table size, index usability, maintenance cost (write-heavy tables get higher penalties), and confidence. Each recommendation includes the affected queries, estimated write cost, existing-index overlap, and ready-to-copy DDL.
Confidence
Each recommendation carries a confidence level: HIGH (simple patterns, high-impact queries), MEDIUM (moderate evidence), or LOW (speculative suggestions like covering indexes). When pg_qualstats is installed, the engine prefers exact predicate columns over regex extraction — those recommendations are always HIGH confidence.
Scope
The engine is intentionally conservative: tables with 3+ non-PK indexes that the planner is bypassing (situation = rich) do not get new-index suggestions. Those cases require plan analysis rather than more indexes.
HypoPG hint
If the hypopg extension is already installed, pocketPG shows a hint for simulating an index before creating it. This is an optional advisory path, not a requirement for the Indexes tab.
Invalid indexes
pocketPG detects indexes that are invalid, not ready, or not live — common after failed CREATE INDEX CONCURRENTLY operations. These indexes are shown separately with their status so they are not confused with healthy but unused indexes.
Index bloat
Estimated index bloat is shown with REINDEX CONCURRENTLY actions when the evidence supports a safe maintenance recommendation.
The bloat table is intentionally action-gated:
- very small indexes are not pushed into rebuild advice just because the percentage looks high
- rebuild suggestions depend on estimated reclaimable space, not percentage alone
- recently rebuilt indexes are marked separately so they do not look like fresh problems
Index sizes
The page also includes a simple top-index-size table so you can see which indexes dominate space, even if they are not currently flagged as unused or bloated.
Caching
Index data is short-lived cached because it is relatively expensive to compute and usually does not change second-by-second.