hypopg

hypopg : Hypothetical indexes for PostgreSQL

Overview

Packages

Source

Install

Make sure PGDG repo available:

pig repo add pgdg -u    # add pgdg repo and update cache

Install this extension with pig:

pig install hypopg;		# install via package name, for the active PG version

pig install hypopg -v 18;   # install for PG 18
pig install hypopg -v 17;   # install for PG 17
pig install hypopg -v 16;   # install for PG 16
pig install hypopg -v 15;   # install for PG 15
pig install hypopg -v 14;   # install for PG 14

Create this extension with:

CREATE EXTENSION hypopg;

Usage

Sources:

• README
• Control
• Release 1.4.4

HypoPG lets you create hypothetical (virtual) indexes that exist only in the current session and are considered by EXPLAIN (without ANALYZE) for query planning. This enables testing the impact of indexes without the cost of actually creating them.

Functions

Workflow

Create a test table and check the baseline plan:

CREATE TABLE hypo AS SELECT id, 'line ' || id AS val FROM generate_series(1, 10000) id;
ANALYZE hypo;
EXPLAIN SELECT * FROM hypo WHERE id = 1;
-- Seq Scan on hypo (cost=0.00..170.00 rows=1 width=15)

Create a hypothetical index:

SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)');
--  indexrelid |      indexname
-- ------------+----------------------
--       13543 | <13543>btree_hypo_id

Check the plan with the hypothetical index:

EXPLAIN SELECT * FROM hypo WHERE id = 1;
-- Index Scan using <13543>btree_hypo_id on hypo (cost=0.04..8.06 rows=1 width=15)

List and manage hypothetical indexes:

SELECT * FROM hypopg_list_indexes();
SELECT * FROM hypopg_drop_index(13543);
SELECT * FROM hypopg_reset();

Limitations

• Only EXPLAIN without ANALYZE will consider hypothetical indexes
• Hypothetical indexes exist only in the current backend session
• Other concurrent connections are not affected
• Index names and some CREATE INDEX options are ignored

1.4.4 and Upgrade Boundaries

Upstream 1.4.4 fixes hypothetical index naming for large OIDs, dependent-object invalidation, INCLUDE-column limits and failed EXPLAIN state, with additional PostgreSQL 19 compatibility fixes. Hypothetical indexes remain local to one backend and affect EXPLAIN without ANALYZE. No extension update scripts are provided: after installing matching files, drop and recreate the extension in each database. Reconnect or reset the backend to discard hypothetical state. This upstream release does not assert a matching packaged build.