Try a Postgres index without HypoPG: CREATE INDEX in a transaction you roll back

You have a slow query and a hunch: an index on (customer_id, created_at) would fix this. But you're not going to CREATE INDEX on production to find out — that takes a lock, does real work, and now you own an index you may not even want.

The usual "proper" answer is HypoPG, an extension that fakes a hypothetical index so the planner thinks it exists. It's great — when you can install it. On managed Postgres, or a prod box you don't own, or a teammate's machine, you often can't. And because the index is fake, HypoPG tells you the planner would use it but never how much faster the query actually runs — there's no real index to execute against.

There's a lower-tech trick that needs no extension and gives you real measured timing: build the index for real, inside a transaction you throw away.

The trick

BEGIN;

-- EXPLAIN ANALYZE actually runs the query — this is the "before" timing.
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ... ;

-- A real index, but visible only inside this transaction.
CREATE INDEX _trial_idx ON orders (customer_id, created_at);

-- Same query again — the planner can now see the index and may pick it.
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ... ;

ROLLBACK;   -- the index is never committed; it vanishes

Postgres lets a CREATE INDEX live inside a transaction, and DDL is transactional: other sessions never see the index, and ROLLBACK drops it. Between the two EXPLAIN ANALYZE runs you get the real before/after — same plan tree, same actual timing — and you can check the thing you actually wanted to know: did the planner choose your index, or ignore it?

That last part is the honest answer. Read the after-plan for an Index Scan on _trial_idx. If it's still a Seq Scan, the index wouldn't have helped — and you learned that for free, in a transaction, on whatever database you happened to have in front of you.

The one gotcha: CONCURRENTLY

You can't run CREATE INDEX CONCURRENTLY inside a transaction — Postgres forbids it. And CONCURRENTLY is exactly what you'd want in production, because it builds the index without holding a write lock on the table. So there's a split:

• Inside the throwaway transaction, build a plain CREATE INDEX (non-concurrent). It briefly takes a stronger lock, but it's your own short-lived session and it's gone on rollback.
• The statement you'd actually run for real should be CREATE INDEX CONCURRENTLY.

This is exactly what cli2ui's "index lab" does. It builds a plain throwaway index to measure against, but the DDL it shows you to copy is the CONCURRENTLY version:

with engine.whatif_cursor() as cur:        # autocommit=False, ALWAYS rolls back
    cur.execute(explain)                   # before
    before = parse_plan(cur.fetchone()[0])
    cur.execute(hypo)                      # plain CREATE INDEX, lives in this tx
    cur.execute(explain)                   # after
    after = parse_plan(cur.fetchone()[0])
    ddl = real.as_string(cur)              # the CONCURRENTLY version, for display
# rollback happens here — the trial index never persists
used = uses_index(after, "_trial_idx")     # did the planner actually pick it?

That whatif_cursor is a single primitive: a transaction that always rolls back (in a finally), with statement_timeout and lock_timeout pre-set so a trial can't hang or sit on a catalog/table lock.

conn.autocommit = False
try:
    with conn.cursor() as cur:
        cur.execute("SET LOCAL statement_timeout = %s", [timeout_ms])
        cur.execute("SET LOCAL lock_timeout = '2s'")
        yield cur
finally:
    conn.rollback()   # the what-if is never persisted

The honest caveats

• A real CREATE INDEX does real work. Unlike HypoPG, you're actually building the index — on a big table that's slow, and that's the price of getting real timing instead of an estimate. For a huge production table, HypoPG's fake index is the better tool; for "I have this table right here, will the index help," this is the faster thing to reach for.
• EXPLAIN ANALYZE executes the query. It runs inside the rolled-back transaction, so writes wouldn't persist either — but it really runs, so don't aim it at a ten-minute query without a statement_timeout.
• The plain build takes a brief lock. A non-concurrent CREATE INDEX locks the table against writes for the duration. In your own short-lived transaction on a dev/staging box that's fine; it's precisely why the production DDL is CONCURRENTLY.
• It's gone on rollback — which is the entire point. Nothing to clean up, nothing left behind on prod, nothing to forget about.

The thing you actually wanted — would this index change the plan, and by how much — answered on the database in front of you, with no extension to install and nothing to undo afterward.

This is one piece of cli2ui — a local-only web UI over the psql commands you keep half-remembering. No AI, no SaaS. It's MIT-licensed on GitHub. What command do you reach for that should be a button?