Index Recommendation Workflows from Plan Regressions

The index-recommendation stage turns a confirmed plan regression into a safe, reviewed CREATE INDEX change-set instead of a raw suggestion that a human has to trust on faith. It consumes the regressed-plan artifact plus live catalog usage stats, detects missing, redundant, and unused indexes, synthesizes candidate DDL, estimates the benefit with a hypothetical index before anything touches disk, and routes the result through review so the Index Sync stage only ever applies a change that has already cleared a numeric benefit bar.

This stage sits inside the Regression Detection & Rule Engines subsystem, downstream of verdict classification and upstream of Index Sync. It never runs EXPLAIN ANALYZE against a primary, never creates a real index, and never applies DDL itself. Its single responsibility is to decide — reproducibly — which index would recover the regression, prove the estimated benefit against a hypothetical plan, and hand a reviewed contract to the applier.

Architectural Boundaries #

The recommendation function is pure with respect to two inputs: the regressed-plan artifact emitted by the verdict stage, and a point-in-time snapshot of pg_stat_user_indexes and pg_stat_user_tables. Given the same artifact and the same catalog snapshot, it must always emit the same candidate DDL and the same benefit estimate. The only side effect permitted inside the evaluation path is the creation and immediate rollback of a HypoPG hypothetical index inside a read-only transaction on a replica — that index exists only in the planner’s memory, occupies no storage, and is discarded before the transaction returns.

Upstream, this stage reads the artifact that carries the offending fingerprint, the filter and join predicates that drove the regressed node, and the cost breakdown produced by Tracking Cost Deltas Across Baseline Versions. It cross-references structural signals from Detecting Join Type Shifts in Execution Plans so a nested-loop that appeared because an index was dropped is scored differently from one caused by stale statistics, and it reads the usage-change signals from Monitoring Index Usage Changes for Regression Signals to distinguish a genuinely missing index from one the planner simply stopped choosing.

Downstream, the stage emits a RecommendationContract — a reviewed DDL change-set with a benefit estimate, a dedup key, and a risk band. The Index Sync applier consumes that contract; it does not re-derive the DDL. Cross-engine cost translation for the benefit numbers is anchored in Cost Estimation Mapping Across PostgreSQL and MySQL so a benefit ratio computed on PostgreSQL means the same thing when the same workflow runs against MySQL.

Any deviation from this contract — creating a real index inside the evaluation path, opening a write transaction on the primary, or letting the applier re-synthesize DDL — breaks reproducibility and moves an unreviewed change closer to production than the design allows.

Deterministic Routing and Schema Enforcement #

Every recommendation is a function of a strictly typed artifact. The engine rejects any artifact that fails schema validation rather than inventing a candidate from partial data, because a recommendation built on a truncated predicate list produces an index that covers the wrong columns.

The incoming regressed-plan artifact is validated against this JSON Schema before candidate detection:

{
  "$id": "https://queryplan.org/schemas/regressed-plan-artifact.json",
  "type": "object",
  "additionalProperties": false,
  "required": ["query_hash", "relation", "filter_columns", "seq_scan", "idx_scan", "seq_tup_read"],
  "properties": {
    "query_hash":     { "type": "string", "pattern": "^[a-f0-9]{64}$" },
    "relation":       { "type": "string", "pattern": "^[a-z_][a-z0-9_.]{0,62}$" },
    "filter_columns": { "type": "array", "items": { "type": "string" }, "minItems": 1, "maxItems": 6 },
    "seq_scan":       { "type": "integer", "minimum": 0 },
    "idx_scan":       { "type": "integer", "minimum": 0 },
    "seq_tup_read":   { "type": "integer", "minimum": 0 }
  }
}

The emitted RecommendationContract is equally rigid: a ddl string carrying exactly one CREATE INDEX CONCURRENTLY statement; a benefit_ratio float in [0.0, 1.0]; a risk_band enum of LOW, MEDIUM, or HIGH; a rule_id naming the branch that fired (MISSING_INDEX, REDUNDANT_INDEX, UNUSED_INDEX, BENEFIT_BELOW_FLOOR, FALLBACK_SKIP); and a dedup key. Two artifacts that target the same relation and column set must produce the same key so a flapping regression never enqueues the same DDL twice:

dedup_key = sha256(relation + "|" + ",".join(sorted(filter_columns)))[:16]

This formula guarantees column-order-independent deduplication — (customer_id, created_at) and (created_at, customer_id) collapse to one candidate for dedup purposes even though DDL synthesis still orders the columns by selectivity. The detection pipeline is a strict, ordered sequence:

1. Candidate Detection — relations whose seq_scan / max(idx_scan, 1) ratio exceeds 10.0 and whose seq_tup_read exceeds 100000 on a table above min_table_rows (10000) become missing-index candidates.
2. DDL Synthesis — the filter columns from the artifact are ordered by estimated selectivity, INCLUDE columns are appended for a covering index, and the statement is emitted as CREATE INDEX CONCURRENTLY.
3. HypoPG Benefit Probe — the hypothetical index is created in planner memory, the regressed query is re-planned, and benefit_ratio = (cost_before − cost_after) / cost_before must clear 0.30.
4. Dedup + Risk Band — the dedup key suppresses repeats and the write-amplification estimate assigns a risk band that gates how much human review the contract requires.

Production-Ready Implementation #

The engine below is an asyncio service. It reads index statistics through an asyncpg pool bound to a read replica, creates and discards a HypoPG hypothetical index inside a rolled-back transaction, instruments every recommendation with OpenTelemetry spans and metrics, and emits structured logs through structlog. No real index is ever created and no statement is executed against the primary.

from __future__ import annotations

import hashlib
import os
from dataclasses import dataclass, field
from typing import Literal

import asyncpg
import structlog
from opentelemetry import metrics, trace

log = structlog.get_logger(__name__)
tracer = trace.get_tracer(__name__)
meter = metrics.get_meter(__name__)

RECO_EMITTED = meter.create_counter("index_reco_emitted_total", unit="1")
BENEFIT_RATIO = meter.create_histogram("index_reco_benefit_ratio", unit="1")
PROBE_DURATION = meter.create_histogram("index_reco_probe_duration_ms", unit="ms")

# --- Detection constants (mirror recommendation.yaml) ---------------------
SEQ_IDX_RATIO_MIN = 10.0
MIN_SEQ_TUP_READ = 100_000
MIN_TABLE_ROWS = 10_000
MIN_BENEFIT_RATIO = 0.30
HIGH_RISK_TABLE_ROWS = 50_000_000


@dataclass(frozen=True)
class RegressedPlanArtifact:
    query_hash: str
    relation: str
    filter_columns: tuple[str, ...]
    seq_scan: int
    idx_scan: int
    seq_tup_read: int


@dataclass
class RecommendationContract:
    ddl: str
    benefit_ratio: float
    risk_band: Literal["LOW", "MEDIUM", "HIGH"]
    rule_id: str
    dedup_key: str
    metadata: dict = field(default_factory=dict)


def _dedup_key(relation: str, cols: tuple[str, ...]) -> str:
    payload = f"{relation}|{','.join(sorted(cols))}".encode()
    return hashlib.sha256(payload).hexdigest()[:16]


def _synthesize_ddl(relation: str, cols: tuple[str, ...], include: tuple[str, ...]) -> str:
    idx_name = f"ix_{relation.replace('.', '_')}_{'_'.join(cols)}"[:63]
    col_list = ", ".join(cols)
    include_clause = f" INCLUDE ({', '.join(include)})" if include else ""
    return (
        f"CREATE INDEX CONCURRENTLY {idx_name} "
        f"ON {relation} ({col_list}){include_clause};"
    )


class RecommendationEngine:
    """Stateless index-recommendation engine over a read-only replica."""

    def __init__(self, pool: asyncpg.Pool) -> None:
        self._pool = pool

    def _is_candidate(self, a: RegressedPlanArtifact) -> bool:
        ratio = a.seq_scan / max(a.idx_scan, 1)
        return ratio > SEQ_IDX_RATIO_MIN and a.seq_tup_read > MIN_SEQ_TUP_READ

    async def _table_rows(self, relation: str) -> int:
        row = await self._pool.fetchrow(
            "SELECT n_live_tup FROM pg_stat_user_tables WHERE relid = $1::regclass",
            relation,
        )
        return 0 if row is None else int(row["n_live_tup"])

    async def _hypo_benefit(self, relation: str, cols: tuple[str, ...], sql: str) -> float:
        # HypoPG creates the index only in planner memory; the tx is rolled back.
        async with self._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"])
                ddl = f"CREATE INDEX ON {relation} ({', '.join(cols)})"
                await conn.execute("SELECT hypopg_reset()")
                await conn.fetch("SELECT indexrelid FROM hypopg_create_index($1)", ddl)
                after = await conn.fetchval(f"EXPLAIN (FORMAT JSON) {sql}")
                cost_after = float(after[0]["Plan"]["Total Cost"])
            finally:
                await tx.rollback()  # discards the hypothetical index
        if cost_before <= 0:
            return 0.0
        return max(0.0, (cost_before - cost_after) / cost_before)

    async def recommend(
        self, artifact: RegressedPlanArtifact, regressed_sql: str
    ) -> RecommendationContract | None:
        with tracer.start_as_current_span("index_reco.recommend") as span:
            span.set_attribute("query_hash", artifact.query_hash)
            span.set_attribute("relation", artifact.relation)
            try:
                if not self._is_candidate(artifact):
                    return None

                n_rows = await self._table_rows(artifact.relation)
                if n_rows < MIN_TABLE_ROWS:
                    RECO_EMITTED.add(1, {"rule_id": "BENEFIT_BELOW_FLOOR"})
                    return None

                cols = artifact.filter_columns
                dedup = _dedup_key(artifact.relation, cols)
                ratio = await self._hypo_benefit(artifact.relation, cols, regressed_sql)
                BENEFIT_RATIO.record(ratio, {"relation": artifact.relation})

                if ratio < MIN_BENEFIT_RATIO:
                    RECO_EMITTED.add(1, {"rule_id": "BENEFIT_BELOW_FLOOR"})
                    span.set_attribute("benefit_ratio", ratio)
                    return None

                risk = "HIGH" if n_rows >= HIGH_RISK_TABLE_ROWS else \
                       "MEDIUM" if n_rows >= 5_000_000 else "LOW"
                ddl = _synthesize_ddl(artifact.relation, cols, include=())
                RECO_EMITTED.add(1, {"rule_id": "MISSING_INDEX", "risk_band": risk})
                span.set_attribute("benefit_ratio", ratio)
                return RecommendationContract(
                    ddl=ddl, benefit_ratio=round(ratio, 4), risk_band=risk,
                    rule_id="MISSING_INDEX", dedup_key=dedup,
                    metadata={"n_live_tup": n_rows, "columns": list(cols)},
                )

            except Exception as exc:  # noqa: BLE001 — fail safe, never emit unreviewed DDL
                span.record_exception(exc)
                log.error(
                    "index_reco_failed",
                    query_hash=artifact.query_hash,
                    relation=artifact.relation,
                    error=str(exc),
                )
                return None


async def build_pool() -> asyncpg.Pool:
    return await asyncpg.create_pool(
        dsn=os.environ["INDEX_RECO_REPLICA_DSN"],
        min_size=2,
        max_size=int(os.environ.get("INDEX_RECO_POOL_MAX", "8")),
        command_timeout=10.0,
    )

Two properties make this safe. First, the benefit probe runs entirely inside a transaction that is always rolled back, so the hypothetical index leaves no trace in the catalog and no page on disk. Second, every failure path returns None rather than a half-formed contract; a corrupt artifact or a HypoPG error can never emit unreviewed DDL, because the only way to reach the applier is a fully validated contract that cleared the benefit floor.

Threshold Reference #

The stage carries operational SLOs independent of the recommendations it produces, and detection thresholds that decide when a scan pattern is worth an index. These are the numbers alerting is wired against.

A sustained emitted-contract rate above 20 per hour almost always means the detection ratio is too loose for the workload, not that the schema is genuinely under-indexed — a fleet does not develop twenty missing indexes an hour. Encode the guardrail as a Prometheus alert:

groups:
  - name: index-recommendation
    rules:
      - alert: IndexRecommendationFlood
        expr: |
          sum(rate(index_reco_emitted_total{rule_id="MISSING_INDEX"}[1h])) * 3600 > 20
        for: 30m
        labels:
          severity: page
        annotations:
          summary: "More than 20 missing-index contracts/hour — detection ratio likely too loose"
          runbook: "Raise seq_idx_ratio_min to 15 or min_seq_tup_read to 250000 before re-enabling auto-enqueue."

      - alert: IndexRecommendationLowBenefit
        expr: |
          histogram_quantile(0.5, sum(rate(index_reco_benefit_ratio_bucket[30m])) by (le)) < 0.15
        for: 15m
        labels:
          severity: warning
        annotations:
          summary: "Median benefit ratio below 0.15 — HypoPG probes are recommending weak indexes"

For deriving the seq/idx ratio and the read-growth signal directly from catalog stats, pair this reference with Detecting Missing Indexes from pg_stat_user_indexes, and for turning a validated candidate into safe DDL see Generating CREATE INDEX DDL from Regressed Plans.

Failure Scenarios and Root Cause Analysis #

1. Redundant index storm after a covering-index rollout. Symptom: the engine recommends (customer_id, created_at) on a table that already carries a leading (customer_id) index. Root cause: the detection ratio fires on seq_scan alone without checking existing prefix indexes, so a partial-coverage table looks fully unindexed. Diagnose by listing existing indexes and their leading columns:

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

Mitigation: before DDL synthesis, drop any candidate whose column prefix is already served by an existing index — a redundant index only adds write amplification.

2. HypoPG benefit inflated by a stale replica. Symptom: benefit_ratio reads 0.80 in the probe but the applied index barely moves p95. Root cause: the replica’s planner statistics lag the primary, so cost_before is computed against outdated row estimates. Diagnose by comparing replica lag against the stats age:

SELECT now() - pg_last_xact_replay_timestamp() AS replica_lag,
       max(last_analyze) AS newest_stats
FROM pg_stat_user_tables;

Mitigation: refuse to probe when replica_lag exceeds 30 seconds; route those artifacts to a queue that waits for the replica to catch up rather than emitting an inflated estimate.

3. Unused-index recommendation churn. Symptom: the engine repeatedly recommends dropping an index that reappears as a candidate a day later. Root cause: idx_scan resets to zero after a pg_stat_reset, so a heavily used index briefly looks unused. Diagnose by checking when stats were last reset:

SELECT stats_reset FROM pg_stat_database WHERE datname = current_database();

Mitigation: require at least 7 days of continuous idx_scan = 0 since the last stats_reset before flagging an index as unused.

4. Dedup key collision across schemas. Symptom: two relations named events in different schemas share one dedup key and only one recommendation is ever enqueued. Root cause: the artifact carried a bare relation name without its schema. Diagnose by asserting the artifact relation is schema-qualified. Mitigation: require the fully qualified schema.relation form in the schema pattern and rebuild the dedup key from it.

5. Probe pool exhaustion under fan-out. Symptom: index_reco_probe_duration_ms climbs and recommendations stop being emitted. Root cause: a burst of regressions opens more concurrent HypoPG transactions than INDEX_RECO_POOL_MAX allows, and command_timeout trips. Diagnose with:

SELECT state, count(*) FROM pg_stat_activity
WHERE application_name = 'index_reco_engine' GROUP BY state;

Mitigation: raise INDEX_RECO_POOL_MAX, add a dedicated replica for probing, and cap concurrent probes with a semaphore so the pool degrades to queuing rather than timing out.

Configuration Reference #

Raise seq_idx_ratio_min and min_seq_tup_read first when chasing recommendation floods; raise min_benefit_ratio when weak indexes are slipping through review. Never loosen detection and lower the benefit floor in the same change — a single-variable move keeps the recommendation set reproducible against replayed artifacts.

Related #

• Detecting Missing Indexes from pg_stat_user_indexes — the catalog-stats runbook that produces the candidate signal this stage consumes.
• Generating CREATE INDEX DDL from Regressed Plans — how a validated candidate becomes safe, correct CREATE INDEX CONCURRENTLY DDL.
• Monitoring Index Usage Changes for Regression Signals — the sibling detector that distinguishes a missing index from one the planner stopped choosing.
• Detecting Join Type Shifts in Execution Plans — the structural signal that separates an index regression from a statistics regression.
• Cost Estimation Mapping Across PostgreSQL and MySQL — anchors the benefit-ratio numbers across engines.

← Back to Regression Detection & Rule Engines