Adding an index looks free in development and gets expensive in production. Every btree you create must be written on every INSERT, maintained on every UPDATE that touches a key column, vacuumed, replicated, backed up, and replayed during crash recovery. On a high-churn table, one speculative index can cost more than the query it was supposed to fix.

HypoPG exists to settle that argument before you pay the bill: it lets you create hypothetical indexes and ask the planner, in a plain EXPLAIN, whether it would use them. Version 1.4.4 shipped on October 26, 2026 (release announcement), and while it is a bug-fix release, two of those fixes matter directly to anyone doing tuning work in a long-lived session.

What a hypothetical index actually is

A hypothetical index lives in your connection’s private memory. It is not written to any catalog, it does not lock or bloat the table, and it cannot affect a concurrent connection. PostgreSQL will only consider it during a plain EXPLAIN — not EXPLAIN ANALYZE — and will never use it to actually execute the query. Supported access methods are btree, brin, hash (PostgreSQL 10+), and bloom (with the bloom extension installed). The extension works on PostgreSQL 9.2 and above.

What changed in 1.4.4

From the changelog, the fixes worth knowing about for tuning work:

• Hypothetical index names are now generated correctly for object identifiers at or above 2^31.
• A memory leak on WITH clause options is fixed — relevant precisely because the natural workflow is one long psql session where you test a dozen candidates in a row.
• Included columns are now checked against the maximum column count, so an oversized INCLUDE list fails loudly instead of producing a nonsense plan.
• Hypothetical indexes are invalidated when a dependent object changes (relation, function, access method, operator class, type). Previously a stale hypothetical index could keep influencing plans after you altered the object underneath it.
• hypopg() now reports indcollation and indclass correctly.
• A failed EXPLAIN no longer affects later queries in the same session.
• The hypothetical OID range lookup is protected against colliding with user objects.
• Additional PostgreSQL 19 compatibility fixes.

That last line is the one to plan around. PostgreSQL 19 is still in release candidate territory (see our RC 1 write-up for the timeline), and HypoPG 1.4.4 is the build to use if you want to test index ideas against the new planner during the RC window rather than after GA.

Step 1: capture the real baseline

Do not start from a query you think is slow. Start from the one whose buffers you can measure.

CREATE EXTENSION hypopg;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 84213
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

Write down the plan shape, the estimated rows, and Buffers: shared hit=… read=…. Buffers are the number you are actually trying to move; cost is only the planner’s opinion. If the estimate and the actual row count are off by an order of magnitude, stop and fix statistics first — every hypothetical result downstream will be meaningless.

Step 2: test candidate indexes without building them

-- candidate A: partial index matching the hot predicate
SELECT * FROM hypopg_create_index(
  'CREATE INDEX ON orders (customer_id, created_at DESC) WHERE status = ''pending'''
);

-- candidate B: full composite index
SELECT * FROM hypopg_create_index(
  'CREATE INDEX ON orders (customer_id, status, created_at DESC)'
);

-- re-run the same EXPLAIN, without ANALYZE
EXPLAIN SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 84213 AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

The plan will name something like <18284>btree_hypo_id. That is your answer: the planner would use it, or it would not. Then price the candidates:

SELECT index_name,
       pg_size_pretty(hypopg_relation_size(indexrelid)) AS est_size,
       hypopg_get_indexdef(indexrelid) AS ddl
FROM hypopg_list_indexes;

Treat est_size as a floor, not a promise — a real index lands larger once fillfactor, alignment, and bloat are in play. It is still the right tool for the decision that matters: is this a 40 MB index or a 4 GB one?

Step 3: audit the indexes you already have

The cheapest tuning win is usually a deletion, not an addition. Hide an existing index and see whether the plan survives:

SELECT hypopg_reset();
SELECT hypopg_hide_index('orders_customer_id_idx'::regclass);

EXPLAIN SELECT ... ;   -- did the plan collapse or just change?

SELECT hypopg_unhide_all_index();

Run this against your real top queries with real parameter values, not synthetic ones. An index that only matters for one admin query is a candidate for removal; one whose removal turns an index scan into a sequential scan over a million rows is earning its keep.

Where HypoPG stops and you have to start

• No EXPLAIN ANALYZE. HypoPG answers “would the planner choose this index”, not “will it be faster”. A hypothetical index can win the cost comparison and lose in production because the cost model does not know about your cache hit ratio, your random I/O, or your correlation.
• It inherits your statistics. Correlated columns and stale pg_statistic produce confident, wrong row estimates — and wrong row estimates produce wrong index advice. ANALYZE first; consider extended statistics on correlated columns.
• Connection-scoped. Hypothetical indexes live in backend memory, so test through a direct psql session. A transaction-mode pooler hands your session to a different backend between statements and the hypothesis disappears with it.
• Roughly 2,500 objects by default. HypoPG borrows from a reserved OID range; set hypopg.use_real_oids = on if you need more, or if you are testing on a standby.

Turning a hypothesis into an index

CREATE INDEX CONCURRENTLY orders_customer_pending_idx
  ON orders (customer_id, created_at DESC)
  WHERE status = 'pending';

-- after a few days of real traffic
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;

Verify with the real EXPLAIN (ANALYZE, BUFFERS) and compare buffers shifted and read, not just wall-clock milliseconds. Then watch the write path for a week: WAL volume, n_tup_upd, autovacuum frequency on that table. An idx_scan of zero after a full traffic cycle — including your month-end or batch window — is a clear instruction to DROP INDEX CONCURRENTLY and move on.

The discipline behind the tool

HypoPG is not really an indexing tool; it is a way to make index decisions falsifiable. You write the query down, you measure the baseline, you test the hypothesis in seconds instead of waiting 40 minutes for a concurrent build, and you keep or discard the index based on evidence. That order matters: plan review first, indexes last.

If your team is sitting on a slow-query queue and a wish list of indexes nobody can agree on, Postgres index and query tuning for Houston teams is where we start — baseline plans and buffer counts before anyone types CREATE INDEX.

Related reading: pg_hint_plan 1.9.0 and plan stability on the way to Postgres 19, and if your candidates are vector indexes rather than btree, HNSW, DiskANN, or no graph at all.