Generating CREATE INDEX DDL from Regressed Plans

Synthesizing an index from a regressed plan is where most automated tuning quietly goes wrong: the columns get the wrong order, the statement omits CONCURRENTLY and takes an ACCESS EXCLUSIVE lock in production, or the new index duplicates one that already exists. This runbook turns a validated missing-index candidate into a correct, safe CREATE INDEX CONCURRENTLY statement — deciding column order by selectivity, adding INCLUDE columns for a covering scan, attaching a partial predicate and operator class where they apply — and proves the benefit against a hypothetical plan before any real DDL is queued.

This guide is the DDL-synthesis step of Index Recommendation Workflows from Plan Regressions; it consumes the candidate produced by Detecting Missing Indexes from pg_stat_user_indexes and emits the reviewed change-set the Index Sync applier executes. Column-order decisions are cross-checked against the structural evidence in Detecting Join Type Shifts in Execution Plans.

Symptom Identification and Production Thresholds #

A DDL statement that is syntactically valid can still be operationally wrong. Treat each of the following as a hard breach condition that blocks the change-set from reaching the applier:

1. Non-concurrent build on a live table. The generated statement lacks CONCURRENTLY on a relation whose n_live_tup is above 100000. A plain CREATE INDEX takes an ACCESS EXCLUSIVE lock; on a hundred-thousand-row table that is a multi-second write stall.
2. Weak covering ratio. The candidate index leaves more than 20% of the query’s projected columns off the index, forcing a heap fetch per row. If the covering ratio is below 0.80, evaluate an INCLUDE list before emitting.
3. Low estimated benefit. The HypoPG cost reduction is below 0.35 — a synthesized index that saves less than a third of plan cost is not worth its write amplification and must be rejected.
4. Duplicate or prefix-redundant index. The synthesized column list is a prefix of, or identical to, an existing index definition. A duplicate index adds write cost and buys nothing.
5. Wide predicate with a partial opportunity. More than 70% of the table’s rows fall outside the query’s filter range (for example, a status = 'pending' predicate on a table that is mostly complete), but the statement builds a full index instead of a partial one. A full index here is several times larger than it needs to be.

When condition 1 or 4 fires, block unconditionally — those are correctness and safety failures, not tuning preferences.

Root Cause Analysis #

Three failure domains produce almost every bad synthesized index. Each has a direct diagnostic.

Wrong column order. A multi-column index must lead with the most selective equality column, then range columns, then sort columns. A generator that preserves the artifact’s column order rather than selectivity produces an index the planner cannot use for the equality lookup. Confirm per-column selectivity from pg_stats before ordering:

SELECT attname,
       n_distinct,
       round((1.0 / NULLIF(n_distinct, 0))::numeric, 6) AS approx_selectivity,
       correlation
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders'
  AND attname IN ('customer_id', 'status', 'created_at')
ORDER BY n_distinct DESC;

Order equality columns by descending n_distinct (a positive n_distinct is a distinct count; a negative value is a fraction of rows), then append the range/sort column last.

Missing CONCURRENTLY or an in-progress duplicate. A plain CREATE INDEX blocks writes; a failed CONCURRENTLY build leaves an INVALID index behind that still consumes space and confuses later synthesis. Check for both existing and invalid indexes on the target:

SELECT indexrelid::regclass AS index_name,
       indisvalid,
       pg_get_indexdef(indexrelid) AS definition
FROM pg_index
WHERE indrelid = 'orders'::regclass
ORDER BY indisvalid, index_name;

Any row with indisvalid = false is a leftover from a cancelled concurrent build and must be dropped before a new statement is queued.

Index bloat and duplication. Synthesizing a new index on a relation that already carries a bloated or prefix-redundant one compounds write cost without improving reads. Diff the candidate columns against existing definitions and check index bloat with pgstattuple before emitting; a candidate whose leading columns match an existing index’s prefix is redundant and must be folded into a reorder rather than added.

Step-by-Step Remediation #

1. Rank columns by selectivity. Read pg_stats for the candidate columns and order equality predicates by descending distinct count, appending range and sort columns last. Never trust the artifact’s column order.

2. Assemble the statement. The synthesizer below builds a CREATE INDEX CONCURRENTLY string with an ordered key, an optional INCLUDE list for covering, and an optional partial WHERE clause. It emits nothing that has not passed the duplicate check.

from __future__ import annotations

import os
from dataclasses import dataclass, field

import asyncpg
import structlog
from opentelemetry import trace

log = structlog.get_logger("ddl.synth")
tracer = trace.get_tracer("ddl.synth")

MIN_BENEFIT = 0.35
LIVE_TUP_CONCURRENT = 100_000


@dataclass(frozen=True)
class IndexCandidate:
    relation: str
    key_columns: tuple[str, ...]        # already ordered by selectivity
    include_columns: tuple[str, ...] = ()
    partial_predicate: str | None = None
    opclass: dict[str, str] = field(default_factory=dict)


def synthesize_ddl(c: IndexCandidate) -> str:
    def render(col: str) -> str:
        return f"{col} {c.opclass[col]}" if col in c.opclass else col

    idx_name = f"ix_{c.relation.replace('.', '_')}_{'_'.join(c.key_columns)}"[:63]
    keys = ", ".join(render(col) for col in c.key_columns)
    include = f" INCLUDE ({', '.join(c.include_columns)})" if c.include_columns else ""
    where = f" WHERE {c.partial_predicate}" if c.partial_predicate else ""
    return (
        f"CREATE INDEX CONCURRENTLY {idx_name} "
        f"ON {c.relation} ({keys}){include}{where};"
    )


async def _is_redundant(pool: asyncpg.Pool, c: IndexCandidate) -> bool:
    rows = await pool.fetch(
        "SELECT pg_get_indexdef(indexrelid) AS d FROM pg_index WHERE indrelid = $1::regclass",
        c.relation,
    )
    lead = ", ".join(c.key_columns[: len(c.key_columns)])
    return any(f"({lead}" in r["d"] for r in rows)


async def _benefit(pool: asyncpg.Pool, c: IndexCandidate, sql: str) -> float:
    async with pool.acquire() as conn:
        tx = conn.transaction()
        await tx.start()
        try:
            before = await conn.fetchval(f"EXPLAIN (FORMAT JSON) {sql}")
            cost_before = float(before[0]["Plan"]["Total Cost"])
            await conn.execute("SELECT hypopg_reset()")
            await conn.fetch(
                "SELECT indexrelid FROM hypopg_create_index($1)",
                synthesize_ddl(c).rstrip(";").replace("CONCURRENTLY ", ""),
            )
            after = await conn.fetchval(f"EXPLAIN (FORMAT JSON) {sql}")
            cost_after = float(after[0]["Plan"]["Total Cost"])
        finally:
            await tx.rollback()
    return 0.0 if cost_before <= 0 else max(0.0, (cost_before - cost_after) / cost_before)


async def build_change_set(pool: asyncpg.Pool, c: IndexCandidate, sql: str) -> str | None:
    with tracer.start_as_current_span("ddl.synth") as span:
        span.set_attribute("relation", c.relation)
        if await _is_redundant(pool, c):
            log.warning("redundant_index_rejected", relation=c.relation)
            return None
        ratio = await _benefit(pool, c, sql)
        span.set_attribute("benefit_ratio", ratio)
        if ratio < MIN_BENEFIT:
            log.warning("benefit_below_floor", relation=c.relation, ratio=round(ratio, 4))
            return None
        ddl = synthesize_ddl(c)
        log.info("change_set_ready", relation=c.relation, ratio=round(ratio, 4), ddl=ddl)
        return ddl


async def main() -> None:
    pool = await asyncpg.create_pool(
        dsn=os.environ["SYNTH_REPLICA_DSN"], min_size=1, max_size=4, command_timeout=10.0,
    )
    try:
        cand = IndexCandidate(
            relation="orders",
            key_columns=("customer_id", "status", "created_at"),
            include_columns=("total_amount",),
            partial_predicate="status = 'pending'",
            opclass={"created_at": "DESC"},
        )
        await build_change_set(pool, cand, "SELECT total_amount FROM orders "
                                            "WHERE customer_id = 42 AND status = 'pending' "
                                            "ORDER BY created_at DESC LIMIT 20")
    finally:
        await pool.close()

Expected output for the covering partial index above:

2026-07-18T10:41:55Z [info] change_set_ready relation=orders ratio=0.612 ddl=CREATE INDEX CONCURRENTLY ix_orders_customer_id_status_created_at ON orders (customer_id, status, created_at DESC) INCLUDE (total_amount) WHERE status = 'pending';

3. Stage the build outside peak hours. Queue the CONCURRENTLY statement for the Index Sync applier; it runs without an exclusive lock but doubles the write work during the build, so schedule it off-peak.

4. Validate after the build. Once applied, confirm the index is indisvalid = true and that the regressed query now chooses it via EXPLAIN.

Verification Checklist #

Work through each item before the change-set is handed to the applier; every box must be checked.

• [ ] The statement contains CONCURRENTLY for any table above 100000 live tuples.
• [ ] Key columns are ordered by descending selectivity, with equality columns before the range/sort column.
• [ ] INCLUDE columns cover the query’s projection so the planner can use an index-only scan.
• [ ] Any partial WHERE predicate matches the query’s filter and excludes the majority partition.
• [ ] The HypoPG benefit ratio for the final statement is at least 0.35.
• [ ] No existing or INVALID index already covers the leading column prefix.
• [ ] Operator classes (for example DESC, text_pattern_ops) match the query’s sort and comparison operators.
• [ ] The generated index name is unique and within the 63-byte identifier limit.

Compatibility and Engine-Specific Notes #

Online index creation and covering-index syntax vary sharply across engines; the synthesizer must branch on the target before rendering DDL.

MySQL has no INCLUDE and no partial index, so a covering index there means widening the composite key and a partial index must be emulated with a generated column plus a filtered secondary index. On distributed engines the build is an asynchronous backfill rather than a synchronous concurrent build, so the applier must poll job status rather than waiting on the statement. Anchor the cross-engine cost translation used by the benefit probe in Cost Estimation Mapping Across PostgreSQL and MySQL.

Related #

• ← Back to Index Recommendation Workflows from Plan Regressions
• Source the candidate this runbook consumes: Detecting Missing Indexes from pg_stat_user_indexes
• Cross-check column order against structure: Detecting Join Type Shifts in Execution Plans
• Confirm the index is actually chosen after apply: Monitoring Index Usage Changes for Regression Signals
• Cross-engine cost mapping: Cost Estimation Mapping Across PostgreSQL and MySQL