HypoPG

What HypoPG does

HypoPG adds a set of functions that create, list, and remove hypothetical indexes. When you call hypopg_create_index() with a standard CREATE INDEX statement, HypoPG registers a virtual index in your session's private memory. The PostgreSQL query planner sees this index and factors it into cost estimates, but no data is read from the table, no index pages are written, and no WAL is generated.

The hypothetical indexes are completely invisible to other sessions. They exist only in the connection that created them and vanish when the session ends. This makes HypoPG safe to use on production databases — there is no risk of interfering with running queries, no locking, and no cleanup required if you forget to drop them.

The key workflow is simple: run EXPLAIN to see the current plan, create a hypothetical index, run EXPLAIN again to see if the planner would use it. If the estimated cost drops significantly, the real index is likely worth creating.

When to use HypoPG

HypoPG is useful whenever you want to answer "would this index help?" without building the index first.

• Validating index candidates — before spending minutes building an index on a large table, confirm in seconds that the planner would actually use it
• Comparing index strategies — test a single-column index vs. a composite index vs. a partial index on the same query and compare the cost estimates
• Auditing existing indexes — create hypothetical alternatives and check if a different column order or a partial WHERE clause would produce a better plan
• Working with pg_qualstats — pg_qualstats identifies predicates with missing index support; HypoPG lets you test the fix before applying it
• Cost-benefit analysis — use hypopg_relation_size() to estimate how large the index would be, then weigh the storage cost against the query improvement

Installation and setup

HypoPG is a third-party extension (not part of PostgreSQL contrib). On Debian/Ubuntu, install the package postgresql-17-hypopg (replace 17 with your PostgreSQL major version). On RHEL/CentOS, the package is hypopg_17. It is also available via PGXN.

No shared_preload_libraries entry is required — HypoPG loads on demand. No server restart needed.

-- Install the extension (no shared_preload_libraries needed)
CREATE EXTENSION hypopg;

-- Verify it's working
SELECT * FROM hypopg_list_indexes();

Creating and testing a hypothetical index

The core workflow: run EXPLAIN, create a hypothetical index, run EXPLAIN again, and compare the plans.

-- First, check the current plan (sequential scan on a large table)
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
--  Seq Scan on orders  (cost=0.00..25412.00 rows=128 width=64)
--    Filter: (customer_id = 42)

-- Create a hypothetical index
SELECT * FROM hypopg_create_index('CREATE INDEX ON orders (customer_id)');
--  indexrelid |          indexname
-- ------------+-----------------------------
--       13543 | <13543>btree_orders_customer_id

-- Check the plan again — the planner now sees the hypothetical index
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
--  Index Scan using <13543>btree_orders_customer_id on orders
--    (cost=0.30..8.45 rows=128 width=64)
--    Index Cond: (customer_id = 42)

The planner switched from a sequential scan (cost ~25,412) to an index scan (cost ~8.45). A three-thousand-fold improvement, arrived at without touching a single data page. The hypothetical index name is prefixed with the OID in angle brackets — this is how you can distinguish hypothetical indexes from real ones in the plan output.

Listing indexes and estimating size

HypoPG provides functions to inspect hypothetical indexes and estimate their on-disk size without creating them.

-- List all hypothetical indexes in the current session
SELECT * FROM hypopg_list_indexes();
--  indexrelid |          indexname           | nspname | relname | amname
-- ------------+-----------------------------+---------+---------+--------
--       13543 | <13543>btree_orders_customer_id | public  | orders  | btree

-- Estimate how large the index would be on disk
SELECT pg_size_pretty(hypopg_relation_size(13543));
--  pg_size_pretty
-- ----------------
--  2544 kB

-- Get the CREATE INDEX statement for a hypothetical index
SELECT hypopg_get_indexdef(13543);
--  CREATE INDEX ON public.orders USING btree (customer_id)

The size estimate uses the same logic PostgreSQL would use to estimate a real index, based on table statistics. A good approximation, though not exact — run ANALYZE on the table first for the most accurate estimate. Knowing the cost in megabytes before committing is a courtesy one's disk budget appreciates.

Advanced index types

HypoPG accepts any valid CREATE INDEX syntax, including composite indexes, partial indexes, and covering indexes.

-- Test a composite index
SELECT * FROM hypopg_create_index(
  'CREATE INDEX ON orders (customer_id, created_at DESC)'
);

-- Test a partial index
SELECT * FROM hypopg_create_index(
  'CREATE INDEX ON orders (status) WHERE status = ''pending'''
);

-- Test a covering index (INCLUDE)
SELECT * FROM hypopg_create_index(
  'CREATE INDEX ON orders (customer_id) INCLUDE (total, created_at)'
);

HypoPG supports B-tree, Hash, GiST, GIN, BRIN, and Bloom access methods. Whatever index type you are contemplating, the odds are good that you can audition it here first.

Cleanup

-- Remove a specific hypothetical index by OID
SELECT hypopg_drop_index(13543);

-- Remove all hypothetical indexes at once
SELECT hypopg_reset();

You do not strictly need to clean up — all hypothetical indexes are automatically removed when the session ends. They are, in that sense, the most courteous of houseguests. These functions are useful when testing multiple index candidates in the same session and you want a clean slate between tests.

Cloud availability

How Gold Lapel relates

I have great respect for HypoPG. It asks the right question — "would this index help?" — and lets you answer it without consequence. That alone puts it ahead of the common practice, which is to build the index, wait twenty minutes, check, regret, and drop it.

Where it asks you to do the work, Gold Lapel does it continuously. The proxy observes every query in flight, identifies patterns that would benefit from an index, evaluates candidates using the same planner cost model that HypoPG leverages, and applies the winning index without waiting for someone to remember to check. The hypothesis, the test, and the action — all attended to on your behalf.

HypoPG remains an excellent tool for ad-hoc exploration during development or incident response. Gold Lapel handles the same class of problem in production, where workload patterns shift and nobody is sitting at a psql prompt to notice.