hypopg — Hypothetical Indexes

Most "missing index" advice tools work the same way. They notice your query has a WHERE clause on an unindexed column and suggest an index on that column. Sometimes that's right. Sometimes the planner would still pick a sequential scan because of selectivity, or pick a different index entirely, or do a hash join that makes the index irrelevant. Heuristics can't tell.

hypopg can. With it installed, MonPG creates a hypothetical index — pure metadata, no disk allocation, no write penalty — and re-runs EXPLAIN on your slow query. The output tells you whether the planner actually picks the new index and how much it changes the cost.

Before: Seq Scan on orders  (cost=0.00..4532.00 rows=50000 width=64)
After:  Index Scan using <<idx_hypo_12345>>  (cost=0.42..8.44 rows=1 width=64)
        -- 535x cheaper

If the planner doesn't pick it, MonPG drops the suggestion. Zero false positives, zero "I added the index and it didn't help" surprises.

Installing

Pure DDL, no SPL change, no restart. The install command is the same everywhere it's available:

CREATE EXTENSION hypopg;

Provider availability:

• AWS RDS, Crunchy, self-hosted — ship it; install directly.
• Azure Flex — available after adding hypopg to azure.extensions in Server parameters.
• Cloud SQL, Aiven, Heroku, Neon — do not ship it. Index Advisor falls back to heuristic-only mode for these clouds.

Why it's safe

The "hypothetical" part is real. hypopg_create_index('CREATE INDEX...') registers an index in PostgreSQL's planner-visible metadata only:

• It doesn't allocate disk.
• It doesn't slow down inserts or updates.
• It doesn't appear in pg_indexes.
• Other sessions can't see it.
• It vanishes when you close the connection or call hypopg_drop_index.

MonPG runs every hypopg call inside a transaction that always rolls back at the end.

The one caveat

The planner's cost estimate for the hypothetical index assumes the index is "warm" in cache. A real new index on a large table needs to be filled and warmed before it actually performs as the planner predicts. Most of the time this isn't a meaningful difference, but on tables in the multi-hundred-GB range it can be.

Recommended workflow

1. Install hypopg.
2. Let MonPG run for at least 24 hours so it has a query corpus.
3. Open Index Advisor → Missing index proposals. Each proposal shows the simulated cost reduction.
4. Sort by simulated cost reduction descending.
5. Accept the clear wins (5x or better cost reduction on queries that run frequently).
6. Run the proposed CREATE INDEX CONCURRENTLY during a low-traffic window.
7. Check back in 24 hours — accepted suggestions should disappear from the list because MonPG sees the new index actually being used.