Skip to content
QueryPilotDocs

Recommendations & validation

A recommendation is a concrete fix for a finding: what to change, why, the SQL to run, its risk and what to watch out for. Recommendations are advisory only. QueryPilot never executes them; an operator reviews the SQL and runs it.

What QueryPilot recommends

Section titled “What QueryPilot recommends”
Type Generated for SQL preview
CREATE_INDEX A sequential-scan finding where no index starts with a filtered column. CREATE INDEX CONCURRENTLY …
ANALYZE_TABLE stale-statistics, and cardinality-mismatch on a table whose statistics are stale. ANALYZE schema.table;
VACUUM_TABLE table-bloat. VACUUM (ANALYZE, VERBOSE) schema.table;
Autovacuum re-enabled table-bloat on a table with autovacuum_enabled = false. ALTER TABLE … RESET (autovacuum_enabled);
DROP_INDEX unused-index, duplicate-index and invalid-index. DROP INDEX CONCURRENTLY …;
QUERY_REWRITE n-plus-one: the shape of a batched query, = $1 becoming = ANY($1). The rewritten query

Other types (CONFIGURATION, INVESTIGATE) exist for findings where the right step is to look closer rather than run a statement.

Every recommendation carries:

  • a risk level: INFORMATIONAL, LOW, MEDIUM, HIGH or DESTRUCTIVE;
  • an expected benefit: UNKNOWN, LOW, MEDIUM or HIGH. QueryPilot never promises a number like “17× faster”. Measured improvement only comes from validation;
  • safety notes, for example that VACUUM makes space reusable but does not return it to the operating system, or that CONCURRENTLY cannot run inside a transaction block.

Only ANALYZE on a table with genuinely stale statistics is suggested for a row-estimate mismatch. When statistics are fresh, re-running ANALYZE would not fix the estimate, so it is not proposed.

Lifecycle

Section titled “Lifecycle”
Status Meaning
PROPOSED Generated and waiting for a decision.
ACKNOWLEDGED Someone is reviewing it.
REJECTED Decided against.
APPLIED An operator ran the SQL and recorded it. Validation starts.
VALIDATING Before and after measurements are being collected.
VALIDATED Validation measured the result.
REGRESSED Validation found the change made the query slower.

Lists show PROPOSED and ACKNOWLEDGED recommendations by default.

Validation

Section titled “Validation”

“It feels faster” is not evidence, and a single fast execution is not enough. When you mark a recommendation applied, QueryPilot measures the affected query before and after that moment and reports one of:

Result Meaning
IMPROVED Latency dropped by more than the minimum change, beyond the noise.
NO_CHANGE The change was smaller than the minimum (10% by default).
REGRESSED Latency rose by more than the minimum, beyond the noise.
INCONCLUSIVE Not enough calls or intervals, too much variance, or an unstable plan. The explanation says which.

How it decides:

  1. Before window: up to 24 hours of measurements before the change was applied.
  2. Settle period: the first minute after applying is ignored, while an index build or cache warm-up settles.
  3. After window: one hour of observation by default. With fewer calls than required by then, observation extends up to 24 hours.
  4. Enough data: at least 30 calls and 4 snapshot intervals on each side.
  5. Beyond the noise: Welch’s t-test on the exact mean latency and standard deviation of each window. Latency is skewed, so the bar is deliberately high: |t| ≥ 3.
  6. Consistency: a mean that improved while the p95 got clearly worse is not called an improvement.

Validation also notes whether the query’s plan changed between the windows, which is usually the reason an index recommendation worked. You can start a new validation run later, for example after traffic returns to normal; it measures the change since the previous run.

Setting Variable Default
validation.observationWindowMs QUERYPILOT_VALIDATION_OBSERVATION_WINDOW_MS 3600000 (1 hour)
validation.maxObservationMs QUERYPILOT_VALIDATION_MAX_OBSERVATION_MS 86400000 (24 hours)
validation.minimumCalls QUERYPILOT_VALIDATION_MIN_CALLS 30
validation.beforeWindowMs file only 86400000 (24 hours)
validation.settleMs file only 60000 (1 minute)
validation.minimumIntervals file only 4
validation.minimumChangePercent file only 10

API

Section titled “API”
Method Path Description
GET /api/v1/databases/{id}/recommendations Recommendations for a database, newest first, with their findings. Query: status, type, risk (comma-separated), limit, cursor. Includes facet counts.
GET /api/v1/recommendations/{id} One recommendation with its lifecycle events and validation runs.
POST /api/v1/recommendations/{id}/{action} acknowledge, apply or reject. For apply, an optional body {"observationMinutes": 5..10080} overrides the observation window. 409 when the transition is not allowed.
POST /api/v1/recommendations/{id}/validate Start a new validation run for an applied recommendation. 409 if it is not applied, already validating, or there is nothing to measure.

Mark a recommendation applied after running its SQL:

Terminal window
curl -s -X POST http://localhost:8080/api/v1/recommendations/<id>/apply \
  -H 'Content-Type: application/json' \
  -d '{"observationMinutes": 120}'