HypoPG

"HypoPG is a PostgreSQL extension adding support for hypothetical indexes." 1.7k★ / 60 forks, read from the repository (T1), pulled from awesome-postgres' Optimization section.

The definition is the whole product: "A hypothetical — or virtual — index is an index that doesn't really exist, and thus doesn't cost CPU, disk or any resource to create." You declare one with hypopg_create_index(), run EXPLAIN, and read whether the planner would have used it.

Why a fake index answers a real question

Building an index on a large table is expensive, takes a lock or a long concurrent build, and tells you nothing until it finishes. The question you actually have — would the planner even choose this index? — is answered entirely by the statistics, which already exist. HypoPG injects a description of the index into the planner's search and lets it cost the plan against the statistics it already has. No data is read, sorted or written.

The limitations follow directly and the README states both. Hypothetical indexes are session-local: "The hypothetical indexes are contained in a single backend. Therefore, if you add multiple hypothetical indexes, concurrent connections doing EXPLAIN won't be bothered by your hypothetical indexes." And, necessarily, "only EXPLAIN without ANALYZE will use hypothetical indexes" — there is nothing to execute, so there can be no actual row counts.

That second limitation is the sharp edge. EXPLAIN alone gives you the planner's estimate with no reality to check it against, and postgresql-planner-statistics is a page about how wrong that estimate can be. So HypoPG tells you which index the planner would pick, and cannot tell you whether the plan would be fast. If the row estimate is off by two orders of magnitude because of correlated columns, HypoPG will confidently recommend an index chosen on the same bad number.

The API beyond creation is small: hypopg_list_indexes to see them, hypopg_drop_index() and hypopg_reset() to remove them, and hypopg_hide_index() / hypopg_unhide_index() — the inverse experiment, asking what the planner would do if an index it currently relies on were gone.

Why it belongs here

It is the third instrument in this spoke's collection, after pev2 and Vitess's VEXPLAIN (cross-shard-queries), and it is the only one that runs the planner on a hypothetical rather than reporting on a real execution. The set now covers three distinct questions: what did this plan cost (query-execution-plan), which machines answered it (vitess), and what would the planner do under a schema that does not exist yet.

It is also an argument for postgresql-extensions' thesis in miniature. Reaching into the planner's index list mid-planning is not an API Postgres advertises for this purpose; the extension seam turned out to be wide enough anyway.

Not covered here. The licence and current release cadence were not read (the README's LICENSE file exists; the type is unconfirmed on this page), and there is no measurement of how closely hypothetical-index recommendations match real post-build performance — which, given the caveat above, is the number that would actually matter.

Related

postgresql-planner-statistics · query-execution-plan · pev2 · postgresql · postgresql-extensions · awesome-postgres · operational-databases

Claim drain, 2026-09-06 — the gap is structural, not merely unmeasured

The claim that nothing measures how closely hypothetical-index recommendations match real post-build performance holds, and the practitioner documentation explains why it is hard to close rather than merely unattempted: hypopg works with EXPLAIN only, and a query that actually executes cannot use a hypothetical index — EXPLAIN ANALYZE plans with it and then runs against the real table, so the timings mean nothing. The recommended workflow is therefore two-stage by construction (hypopg for planner intent, then CREATE INDEX CONCURRENTLY and EXPLAIN ANALYZE on staging). A study comparing estimate to outcome would have to build every index it hypothesised, which is the cost hypopg exists to avoid. That is the reason the number is missing, and it belongs on the page.