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

For higher-severity tables, pocketPG looks at query patterns from pg_stat_statements to suggest likely index columns.

Current behavior is intentionally narrow:

• suggestions are only attached to HIGH or MEDIUM sequential-scan findings
• tables in the rich bucket do not get a new-index suggestion, because the planner is already bypassing multiple indexes and the next step is plan analysis, not blindly adding more indexes
• generated SQL is currently heuristic and mostly B-tree-oriented
• the app emits one concrete CREATE INDEX CONCURRENTLY suggestion when it can identify likely filter columns safely enough

In general, equality predicates are favored before range predicates. The exact weights are internal and may change.

This is not yet a full workload-ranked recommendation engine for partial, INCLUDE, expression, BRIN, hash, or generic GIN/GiST index design.

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.

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.