Skip to content

SYSTEM Cited by 1 source

HypoPG

What it is

HypoPG (github.com/HypoPG/hypopg) is an open-source Postgres extension that creates hypothetical indexes — indexes that "do not actually exist (and therefore have no overhead) but which the planner can use in the context of EXPLAIN commands" (sources/2026-04-21-planetscale-ai-powered-postgres-index-suggestions).

The extension exposes a function that registers an index definition as if it had been created, and then the Postgres planner treats that index as usable during EXPLAIN cost estimation without any on-disk storage, build cost, or write-side maintenance cost.

Why it shows up on this wiki

HypoPG is the validation oracle behind PlanetScale's Insights AI-powered index suggestions feature. The canonical workflow:

  1. An LLM generates candidate CREATE INDEX statements from workload + schema context.
  2. Each candidate is registered with HypoPG as a hypothetical index.
  3. For every query the candidate is supposed to help, EXPLAIN is run twice — with and without the hypothetical index — and the planner's estimated cost is compared.
  4. A candidate is only surfaced to the customer if the planner agrees the index would materially reduce cost for at least one query.

See concepts/hypothetical-index-evaluation and patterns/llm-plus-planner-validation for the surrounding patterns.

Why a hypothetical index matters

Normal index tuning requires actually creating the index to measure its effect — a step that costs I/O, memory, storage, and write overhead, and can lock the table or throttle live traffic. HypoPG side-steps this entirely:

  • No physical build — the index doesn't exist on disk, so there's no rewrite cost, no storage cost, no I/O.
  • No write-path tax — DML on the table isn't slowed; the secondary-index write tax is not paid.
  • Uses the real planner — it's not a synthetic cost-estimator, it's Postgres's own EXPLAIN output, so estimates reflect the real planner's choices.

Trade-off: HypoPG estimates are planner estimates, not measured execution times. The planner's cost model is an approximation; real speed-ups after applying the index can differ. For candidate-filtering use ("is this index worth recommending?") the approximation is good enough — the purpose is to reject useless suggestions cheaply, not to produce a precise speed-up figure.

Seen in

  • sources/2026-04-21-planetscale-ai-powered-postgres-index-suggestions — canonical wiki instance. PlanetScale Insights uses HypoPG as the second validation phase for LLM-generated CREATE INDEX statements. Quoted verbatim: "HypoPG lets us create hypothetical indexes that do not actually exist (and therefore have no overhead) but which the planner can use in the context of EXPLAIN commands."

Source

Last updated · 319 distilled / 1,201 read