HypoPG: Testing PostgreSQL Indexes Without Creating Them

Why HypoPG Changes Index Evaluation

HypoPG addresses a specific PostgreSQL planning problem: determining whether an index would change a query plan before paying the cost of creating that index in the database. The extension accepts a regular CREATE INDEX statement, registers an index that exists only in the planner's model, and makes that index available to EXPLAIN. According to the README, the resulting hypothetical index consumes no CPU, disk, or other resources to create.

The intended users are database engineers and PostgreSQL administrators investigating problematic queries. A common workflow is to model an index on a filtered or frequently accessed column, inspect the resulting plan, and then decide whether a real index deserves the resources it would require. HypoPG also supports the reverse experiment by hiding existing indexes and observing which alternative PostgreSQL selects. That makes it useful for understanding why an existing plan is being chosen, not merely for proposing a new access path.

This is a planning tool, not a benchmark harness. The README states that only EXPLAIN without ANALYZE uses hypothetical indexes. A query run with EXPLAIN ANALYZE executes the real database operation and does not obtain a measured result from the virtual index. Hypothetical indexes therefore answer whether PostgreSQL's planner considers an index attractive under its current cost model. They do not establish that the index will improve wall-clock execution time in the production environment.

Inside HypoPG's Planner Model

The extension is implemented as PostgreSQL extension code in C, with the repository exposing objects such as hypopg.c, hypopg_index.c, SQL installation files, and a hypopg.control file. Its main mechanism is a planner-visible catalog model. A SQL function, hypopg_create_index, receives a CREATE INDEX statement as text. The README's example uses a column named id on a table named hypo. The extension then makes a corresponding index-like entry available for planning, with an index name that identifies it as hypothetical.

HypoPG provides more than the high-level creation function. The README says that hypopg() returns hypothetical indexes in a form similar to the pg_index system catalog, which gives users a lower-level view for technical inspection. hypopg_list_indexes shows entries including the index relation identifier, generated index name, schema name, table name, and access method. The documented output uses btree for the example index.

The modeled index is scoped to one PostgreSQL backend. The README explicitly says that hypothetical indexes added by one backend remain isolated from concurrent connections performing EXPLAIN. This isolation prevents unrelated sessions from seeing experimental index definitions. It also means that a hypothetical index is not a shared, database-wide planning change. A developer must create it in the same backend in which the relevant EXPLAIN is issued.

HypoPG can also hide real indexes with hypopg_hide_index and restore them with hypopg_unhide_index. Hiding works through an OID or a REGCLASS value. The README shows that, after one real index is hidden, the planner can select another index; after all alternatives are hidden, it can return to a sequential scan. This gives users a way to isolate the effect of a particular physical index without immediately dropping it.

Installing HypoPG and Creating the First Model

The README specifies compatibility with PostgreSQL 9.2 and above, requires PostgreSQL header files, and gives a source installation procedure. The build uses PostgreSQL's PGXS mechanism through the repository Makefile. Decompress the tarball, then install the compiled extension with this documented command:

sudo make install

After installation, create the extension in every database where it is needed:

CREATE EXTENSION hypopg;

The README does not document package-manager commands, Docker images, Windows installers, or a server configuration requirement. It also does not specify a database initialization command beyond CREATE EXTENSION. The target PostgreSQL environment must provide the header files used during compilation.

For the first experiment in the README, create this sample table:

CREATE TABLE hypo AS SELECT id, 'line ' || id AS val FROM generate_series(1,10000) id;

The table has 10,000 generated rows, an integer id, and a val column. The README's initial EXPLAIN produces a sequential scan with an id filter. Next, register a hypothetical index using the regular CREATE INDEX syntax as the function argument:

SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');

The function returns information about the modeled index rather than creating a physical access path. Some CREATE INDEX information can be ignored, including an index name supplied by the user. For statements that require quoting, the README recommends PostgreSQL dollar-quoting syntax:

SELECT * FROM hypopg_create_index($$CREATE INDEX ON hypo (id) WHERE val = 'line 1'$$);

Inspect the available models in the same backend with:

SELECT * FROM hypopg_list_indexes ;

The documented result includes indexrelid, index_name, schema_name, table_name, and am_name. Its generated name follows a form such as <50573>btree_hypo_id. For more technical inspection, the README says hypopg() returns hypothetical indexes in a way similar to the pg_index system catalog.

Reading HypoPG EXPLAIN Results and Cleanup

With the hypothetical index registered, run the same query again:

EXPLAIN SELECT * FROM hypo WHERE id = 1;

The README shows a sequential scan before the model is registered. After registration, it shows an Index Scan using a generated name such as <41072>hypo_btree_hypo_id, with the id equality as the index condition. This demonstrates the specific decision HypoPG supports: PostgreSQL's planner considers the modeled access path and changes the selected plan under its current cost model.

The same query with ANALYZE follows a different path:

EXPLAIN ANALYZE SELECT * FROM hypo WHERE id = 1;

The documented plan is a sequential scan containing actual time, rows, and execution time values. The README states that only EXPLAIN without ANALYZE uses hypothetical indexes. Consequently, a hypothetical EXPLAIN result cannot answer how fast a physical version of that index would execute the query. HypoPG is also unable to report index build time, physical storage consumption, or the operational cost of maintaining the index, because those resources are absent from the model.

Hypothetical indexes remain until removal or connection closure. For one modeled index, pass its OID from hypopg_list_indexes to hypopg_drop_index(indexrelid). To remove all of them at once, run:

SELECT hypopg_reset();

Closing the current connection has the same cleanup effect for its modeled indexes. The backend boundary also affects experimentation. An index registered through hypopg_create_index in one session is not included in EXPLAIN performed by another session. Definition, plan inspection, hiding, and cleanup therefore need to account for session scope.

The hidden-index API offers a second form of investigation. After creating real indexes and checking the baseline plan, users can hide an index by passing its REGCLASS to hypopg_hide_index. The planner can then select another access path or return to a sequential scan. hypopg_unhide_index restores the index. The README recommends clearing prior effects with hypopg_reset before starting such a comparison. hypopg_hidden_indexes() lists the hidden entries.

HypoPG's Constraints and Upgrade Cost

The principal constraint is that HypoPG models planner behavior rather than measuring a physical index. It can show whether PostgreSQL chooses a proposed index under current cost assumptions, but the README does not establish that selecting the plan guarantees lower production latency. Parameter distribution, statistics, concurrent activity, physical index size, cache behavior, and write costs can differ from the hypothetical case. None of those runtime effects can be established from HypoPG's EXPLAIN output alone.

Backend scoping is another operational boundary. HypoPG keeps hypothetical indexes in one backend, so concurrent EXPLAIN calls from other connections do not see those indexes. That isolation is useful for testing, but it makes multi-session scripts unreliable if creation and planning happen in different connections. Closing the connection cleans up the models, yet reconnecting also removes access to them.

Upgrade handling adds administrative work. The README says HypoPG provides no extension upgrade scripts because its objects save no data. The prescribed upgrade method is to drop the extension and create it again with the new version. The README does not provide an automated rollback procedure, nor does it document a package-manager upgrade path. Administrators must account for the extension objects and functions during removal and recreation.

Release 1.4.3 was published on 2026-06-19. Earlier listed releases include 1.4.2 on 2025-06-29 and 1.4.1 on 2024-04-28. The repository's last push was on 2026-10-11. The repository is not archived, but those dates do not guarantee compatibility with a particular PostgreSQL installation or operating system. The README states compatibility with PostgreSQL 9.2 and above, and the Makefile uses PGXS through pg_config.

The LICENSE is a custom license that GitHub cannot classify. The repository includes a LICENSE file, but its terms are not reproduced in the README. Prospective users therefore need to read that file before redistribution or organizational adoption. The repository information alone does not support further claims about legal permissions or obligations.

HypoPG Compared with Physical Index Testing

The direct alternative is to create a real index with PostgreSQL's CREATE INDEX command and inspect its query plan. A physical index can appear in both EXPLAIN and EXPLAIN ANALYZE, and a controlled test can expose real execution behavior. The trade-off is resource use. Creating an index requires work and storage, while maintaining one affects database operations. HypoPG avoids that physical change by changing only the planning choices available to the current backend.

This difference makes the tools suitable for different stages. HypoPG answers whether PostgreSQL's planner selects a candidate definition under its current model. It is useful for screening several access paths, investigating the role of an existing index through hiding, or checking a partial-index predicate without immediately building the structure. Physical index testing answers what happens when the server actually has the candidate structure available.

A sensible workflow can use both. Start with HypoPG to narrow the definition, review EXPLAIN, and eliminate definitions the planner will not select. Create a physical version of a surviving candidate in an appropriate test database when measured execution is needed. The README does not document a mechanism for automatically transferring a hypothetical index definition into production, so that transition remains a separate PostgreSQL index operation.

HypoPG is the wrong tool when the required decision depends on elapsed time, actual rows read, index size, build duration, or write overhead. It is also unsuitable for communicating an index choice through one database-wide object, because its hypothetical entries are backend-scoped. Its narrower purpose is planner experimentation without the immediate resource commitment of a physical index.

Editorial conclusion

Database engineers should consider HypoPG when they need a low-cost way to inspect PostgreSQL planner decisions for proposed indexes or hidden alternatives. Developers who require measured execution behavior should not treat a hypothetical plan as proof of production speed, since the README limits hypothetical indexes to EXPLAIN without ANALYZE. Before adoption, verify compatibility with the target PostgreSQL installation, inspect the custom LICENSE file, and confirm that the planner assumptions behind each proposed index match the intended workload.

Frequently asked questions

Official sources

1. HypoPG/hypopg on GitHub
2. Issues
3. Project website
4. README
5. Releases