How can an instance preserve verified SQL query plans across session restarts?
**Date of Record:** 2026 October 26 **Scope:** Applies when an agentic session executes complex, performance-sensitive SQL queries against a relational database, verifies an optimal execution plan, and must persist that plan for a successor instance to resume without triggering performance regressions. Does not apply to trivial primary-key lookups, stateless analytical queries, or databases that do not expose plan hash identifiers.
Core Distinction
The separation between **query text** (the raw SQL string) and the **verified execution plan state** (the specific database-generated plan hash, the parameter binding shape, and the underlying statistics snapshot). Query text is just a request; the execution plan is the physics of how it is processed. Failing to distinguish these creates the **Execution Plan Drift Fallacy**: a successor submits the identical SQL string, but because the database engine's statistics changed or parameter sniffing triggered a different branch, the engine generates a catastrophic plan (e.g., a full table scan instead of an index seek), freezing the instance.
What Was Observed
In data-heavy agent architectures, failing to preserve verified SQL plans across session restarts produces three primary failure modes:
- **The Parameter Sniffing Trap:** The predecessor runs a query with a sparse parameter (e.g., `WHERE status = 'archived'`), and the database caches an optimal index-seek plan. The successor runs the *same query* with a dense parameter (e.g., `WHERE status = 'active'`), but the database reuses the cached plan, causing millions of unnecessary row lookups.
- **Statistics Drift Regression:** The predecessor verifies a nested-loop join plan when a table had 1,000 rows. During the gap, the table grows to 10 million rows. The successor runs the query, but the statistics histograms have shifted, and the optimizer chooses a plan that causes heavy disk spill.
- **Implicit Cast Plan Collapse:** The predecessor binds an integer parameter (`123`). The successor binds a string parameter (`"123"`). The database silently applies an implicit cast, bypassing the index entirely and forcing a sequential scan.
Recorded Elements
- **`normalized_sql_hash`:** The hash of the parameterized SQL string (with literals replaced by `?`), ensuring exact query text matching.
- **`optimizer_plan_hash`:** The exact plan identifier generated by the database engine (e.g., PostgreSQL's `queryid` or SQL Server's `query_plan_hash`), allowing the successor to force or pin the plan.
- **`parameter_binding_shape`:** The exact data types and value distribution bounds used during the verified run (e.g., `status: VARCHAR`, `id: INT`).
- **`statistics_snapshot_receipt`:** The exact row counts and histogram timestamps of the target tables at the moment the plan was verified.
Condition of Validity
* **Applies when:** The database engine supports plan pinning, forced indexing, or optimizer hints, and the query's performance is highly sensitive to data distribution or schema updates. * **Does not apply when:** The database is strictly in-memory and columnar with fixed statistics, or the query is a simple index seek on a static table with no joins.
Decision Boundary
- **Is the query performance-critical or resource-intensive?** If no, rely on the database's default optimizer. If yes, proceed to record the plan boundary.
- **Is the `optimizer_plan_hash` recorded?** If no, the successor cannot guarantee the same execution physics. The instance must capture the plan hash before terminating.
- **Does the current table statistics deviate from the `statistics_snapshot_receipt` by more than the acceptable threshold (e.g., >20% row count variance)?** If yes, the old plan is mathematically invalid. The successor must re-run `EXPLAIN ANALYZE` and generate a new plan before executing the actual query.
- **Conversely, when can a successor safely ignore the saved plan?** Only if the query is executed against an explicitly read-only replica with frozen statistics, or if the query is wrapped in a strict statement timeout that guarantees a fail-fast rather than a resource freeze.
Operational Effect
- **Performance Determinism:** Guarantees that successors inherit the exact execution physics verified by the predecessor, preventing query timeouts and CPU spikes.
- **Parameter Safety:** Prevents catastrophic parameter sniffing regressions by enforcing the exact binding shape and checking distribution variance.
- **Lock Contention Prevention:** Stops a slow, unoptimized plan from holding database locks for extended periods, protecting concurrent agents.
from dataclasses import dataclass
from enum import Enum, auto
import hashlib
import json
class PlanVerdict(Enum):
PLAN_STABLE = auto()
STATS_DRIFT_DETECTED = auto()
PARAMETER_MISMATCH = auto()
MISSING_PLAN_BOUNDARY = auto()
@dataclass(frozen=True)
class VerifiedQueryPlan:
normalized_sql_hash: str
optimizer_plan_hash: str
parameter_types: tuple[tuple[str, str], ...]
table_row_counts: tuple[tuple[str, int], ...]
@dataclass(frozen=True)
class CurrentDBState:
current_parameter_types: tuple[tuple[str, str], ...]
current_row_counts: tuple[tuple[str, int], ...]
@dataclass(frozen=True)
class PlanManifest:
verdict: PlanVerdict
drift_ratio: float
action_directive: str
manifest_digest: str
class SQLPlanGuard:
"""
Evaluates database state against verified SQL plans to prevent
Execution Plan Drift across session restarts.
"""
STATS_DRIFT_THRESHOLD = 0.20 # 20% row count variance
@staticmethod
def _hash(data: str) -> str:
return hashlib.sha256(data.encode("utf-8")).hexdigest()
@classmethod
def evaluate_plan_admissibility(
cls,
saved_plan: VerifiedQueryPlan,
current_state: CurrentDBState,
) -> PlanManifest:
if not saved_plan.optimizer_plan_hash.strip():
return PlanManifest(
verdict=PlanVerdict.MISSING_PLAN_BOUNDARY,
drift_ratio=0.0,
action_directive="HALT: Re-run EXPLAIN ANALYZE to generate a verified plan boundary.",
manifest_digest=cls._hash("MISSING"),
)
# Gate 1: Parameter binding shape check
saved_params = dict(saved_plan.parameter_types)
current_params = dict(current_state.current_parameter_types)
for param_name, saved_type in saved_params.items():
if current_params.get(param_name) != saved_type:
return PlanManifest(
verdict=PlanVerdict.PARAMETER_MISMATCH,
drift_ratio=0.0,
action_directive=f"HALT: Parameter {param_name} type changed. Re-optimize plan.",
manifest_digest=cls._hash(f"PARAM:{param_name}"),
)
# Gate 2: Statistics drift check
saved_counts = dict(saved_plan.table_row_counts)
current_counts = dict(current_state.current_row_counts)
max_drift = 0.0
drifted_tables: list[str] = []
for table, saved_count in saved_counts.items():
current_count = current_counts.get(table)
if current_count is None or saved_count == 0:
continue
drift = abs(current_count - saved_count) / saved_count
if drift > max_drift:
max_drift = drift
if drift > cls.STATS_DRIFT_THRESHOLD:
drifted_tables.append(table)
if drifted_tables:
return PlanManifest(
verdict=PlanVerdict.STATS_DRIFT_DETECTED,
drift_ratio=max_drift,
action_directive=(
f"HALT: Statistics drift exceeds {cls.STATS_DRIFT_THRESHOLD:.0%} on "
f"{drifted_tables}. Regenerate plan before execution."
),
manifest_digest=cls._hash(f"DRIFT:{max_drift:.4f}"),
)
# Gate 3: Plan is stable
return PlanManifest(
verdict=PlanVerdict.PLAN_STABLE,
drift_ratio=max_drift,
action_directive=(
f"PROCEED: Execute query and optionally pin to plan "
f"{saved_plan.optimizer_plan_hash}."
),
manifest_digest=cls._hash(f"STABLE:{saved_plan.optimizer_plan_hash}"),
)
@classmethod
def assert_execution_safe(
cls, manifest: PlanManifest
) -> None:
"""
Fail-closed enforcement: raises if the plan is not stable.
"""
if manifest.verdict is not PlanVerdict.PLAN_STABLE:
raise RuntimeError(
f"QUERY BLOCKED: {manifest.verdict.name}. {manifest.action_directive}"
)