Findings

A finding is a problem QueryPilot detected, with the evidence behind it: which numbers, over which window, from which rule. Findings are produced by deterministic rules. There is no model guessing at your data, and every threshold can be tuned.

How analysis runs

• The worker schedules one analysis job per database per minute by default (worker.analysisIntervalMs). However many workers run, each database is analysed once per interval.
• Each run loads the recent window of collected telemetry (query statistics, plans, activity, locks, connections, table and index statistics) and evaluates every enabled rule against it.
• Rules are conservative on purpose. A finding is surfaced only above a minimum confidence (analysis.minimumConfidence, default 0.5), and most rules require enough calls, rows or bytes for the problem to matter.
• Running a rule again on the same problem updates the existing finding instead of creating a new one.

Analysis can be switched off globally with QUERYPILOT_ANALYSIS_ENABLED=false, and any rule can be disabled individually in the configuration file:

querypilot.yaml

analysis:
  rules:
    unused-index:
      enabled: false

Severity

Every finding has a severity: INFO, LOW, MEDIUM, HIGH or CRITICAL. Severity is decided by the rule from how far past its threshold the problem is. For example, a query regression is MEDIUM at the threshold, HIGH when p95 latency rose by 300% or more, and CRITICAL at 1000% or more.

Detection rules

Thresholds are the defaults from analysis.thresholds.*; see the configuration reference.

Queries

Rule	Detects	Default thresholds
query-regression	A query whose mean and estimated p95 latency rose compared with the window just before. Both must rise, so a single noisy statistic is not enough.	p95 up 100% · p95 at least 50 ms · 20 calls in both windows
sequential-scan	A filtered sequential scan on a large table that returns a small fraction of it, run often. Says whether no index starts with a filtered column, or one exists and the planner still chose a scan.	100,000 table rows · 100× more rows scanned than returned · 60 calls/hour
cardinality-mismatch	Plan nodes whose row estimate is far from the actual row count, a common cause of bad plans.	10× off · 1,000 rows
expensive-sort	Slow sorts, including sorts that spill to disk.	500 ms
nested-loop	Nested loops with many iterations that take a long time.	1,000 loops · 500 ms
high-io	Queries that read most of their blocks from disk instead of shared buffers.	read ratio 0.5 · 10,000 blocks read
n-plus-one	A single-row lookup that runs once per row of another query. See N+1 detection.	ratio 5 · 600 child calls/hour

Plan-based rules use the captured or uploaded plans of each query. Estimated plans carry lower confidence than executed (EXPLAIN ANALYZE) plans.

Sessions and locks

Rule	Detects	Default thresholds
lock-contention	Sessions waiting on locks held by other transactions, grouped by the contended table, so a hot table gives one finding with a count.	wait 5 s · critical 30 s
long-transaction	Long-running transactions, and sessions idle in transaction, which hold locks and stop vacuum while doing nothing.	active 60 s / 300 s · idle 30 s / 120 s
connection-saturation	Client connections approaching max_connections, QueryPilot’s own included.	70% · 80% · 90% · 95%

Lock wait durations are upper bounds: PostgreSQL records when the waiting statement started, not when the wait began. The evidence says so.

Tables and indexes

Rule	Detects	Default thresholds
table-bloat	Dead rows piling up faster than autovacuum removes them, judged against the table’s effective autovacuum threshold, including per-table settings. Flags tables with autovacuum turned off and names long transactions that may be holding vacuum back.	20% dead · 10,000 dead rows · 2× the autovacuum threshold
stale-statistics	Tables where far more rows changed since the last ANALYZE than autoanalyze allows, so the planner estimates from an old distribution.	2× the autoanalyze threshold · 10,000 rows
xid-wraparound	Tables whose oldest unfrozen transaction ID is approaching the limit where PostgreSQL stops accepting writes.	50% of the limit (HIGH) · 75% (CRITICAL)
unused-index	Indexes with no scans over the whole observation period. They cost writes and disk for nothing.	7 days observed · 10 MB
duplicate-index	Indexes made redundant by another index. The more-scanned index is the one to keep.	1 MB
invalid-index	Indexes marked invalid, usually left behind by a failed CREATE INDEX CONCURRENTLY.	none

N+1 detection

PostgreSQL has no request traces, so N+1 detection is an inference from aggregate statistics, and the finding says so. The signature is arithmetic: an application runs a parent query that returns N rows, then a single-row child lookup once per row, so over any interval

child calls ≈ parent calls × parent rows per call

A pair of queries is reported only when all of these hold:

• the child is a single-row key lookup (one = $n predicate, at most 2 rows per call);
• the child runs at least 5 times per parent call, and at least 600 times an hour;
• that ratio matches the parent’s own rows per call, within 25%;
• the ratio is stable across the window (coefficient of variation at most 0.35);
• when call rates vary enough to measure, they move together (correlation at least 0.8).

It is HIGH when the child accounts for at least 10% of execution time or runs 100,000 times an hour or more.

Statuses and actions

Status	Meaning
OPEN	Detected and not yet handled.
ACKNOWLEDGED	Someone is looking at it.
RESOLVED	Marked as fixed. Reopens automatically if analysis detects the problem again.
IGNORED	Deliberately not acted on.

Lists show active findings (OPEN and ACKNOWLEDGED) by default. Each finding links to the queries, tables or indexes it affects and to any recommendations generated for it.

API

Method	Path	Description
GET	/api/v1/databases/{id}/findings	Findings for a database, most severe first. Query: status, severity, type (comma-separated), search, queryId, limit, cursor. Includes facet counts.
GET	/api/v1/findings/{id}	One finding with its evidence and affected queries.
POST	/api/v1/findings/{id}/{action}	acknowledge, resolve, ignore or reopen. 409 when the transition is not allowed.