Rank by Mean or Rank by Tail: A Queue Rule for SQL Review Agents

A checkout API spent fourteen minutes under a held row lock during a routine afternoon deploy. The SQL review agent had already cleared the hottest query by mean latency that same morning. The blocking statement was a rare report join whose average looked cheap in pg_stat_statements. This article treats that incident pattern as a ranking problem rather than a model-quality problem.

The queue is the hidden prompt

Most SQL review agents do not read every statement that reached the database. They receive a shortlist, and that shortlist is usually ordered by mean execution time from pg_stat_statements. Mean time is cheap to query, stable across many calls, and already sitting in shared catalogs. It is also a biased prior when lock waits and parameter sniffing live in the tail.

PostgreSQL does not store a true p95 inside pg_stat_statements for any queryid. The extension exposes mean_exec_time, stddev_exec_time, min_exec_time, and max_exec_time on each normalized fingerprint. If an agent ranks only on mean_exec_time, it will starve statements that stay cheap on average and become catastrophic on the worst call. That starvation is a property of the queue, not a property of the language model.

Position A: rank the review queue by mean_exec_time

Position A treats mean latency as the least noisy ranking key available without extra logging. High-call statements with elevated means are where CPU, buffer cache, and planner cost usually concentrate. Reviewing them first maximizes expected work avoided per token spent on later analysis. The ranking key stays inside the catalog, so the pipeline needs no log shipper and no percentile store.

Advocates of Position A also note that max_exec_time is a single observation, not a distribution. One checkpoint stall or one cold cache fill can inflate max without describing the steady state that the cluster actually pays. Mean time, especially once calls pass a few hundred, is closer to the planner's own average-cost view. For agents that propose indexes, that average is a better match than a one-off spike.

Position A therefore ships a shortlist of the top N queryids by mean_exec_time, filtered by a minimum calls threshold. The agent never sees the rare report that blocked checkout unless that report also carries a high mean. That is a deliberate trade: stability and token efficiency over tail coverage. Teams with clean OLTP and no ad-hoc reporting often accept that trade.

Position B: rank by a tail proxy, then break ties with mean

Position B starts from a catalog fact: a lock spike is a tail event, not an average event. mean_exec_time can stay modest while max_exec_time or stddev_exec_time explodes on the same queryid. Review agents that ignore those columns keep polishing popular sequential scans and miss the join that holds a primary row. The mean is not wrong; it is answering a different question than on-call is asking.

A practical tail proxy, without claiming a real percentile, is mean_exec_time plus k times stddev_exec_time, with max_exec_time as a circuit breaker. Large standard deviation relative to mean is a signal of parameter sniffing, wait events, or plan flips across binds. Position B ranks by that proxy, then uses mean_exec_time only as a tie breaker among stable fingerprints. k equals two in the example below; it is a gate, not a statistical proof.

Position B accepts more false positives on purpose. Checkpoint noise and one-off maintenance will enter the review queue on noisy days. The operational claim is that a false positive review is cheaper than a missed lock on a writer row. That claim should be tested against your lock_timeout logs, not against a model vendor's demo.

Evidence you can reproduce on a replica

The following query is a labeled example for PostgreSQL 14 and later with pg_stat_statements installed. Run it on a replica or a restored snapshot, never as a writer-side experiment under lock pressure. Do not paste raw literals from production into an external prompt when those literals can carry PII or tenant keys.

-- labeled example: ranking keys, not a percentile estimator
SELECT
  queryid,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  round(max_exec_time::numeric, 2) AS max_ms,
  round((mean_exec_time + 2 * stddev_exec_time)::numeric, 2) AS tail_proxy_ms,
  CASE
    WHEN mean_exec_time > 0
      THEN round((stddev_exec_time / NULLIF(mean_exec_time, 0))::numeric, 2)
    ELSE NULL
  END AS cv,
  left(query, 120) AS query_head
FROM pg_stat_statements
WHERE calls >= 50
  AND dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY mean_exec_time DESC
LIMIT 20;

Compare that ordering with the same select ordered by tail_proxy_ms descending. The two lists diverge on any workload that mixes waits, bind-sensitive plans, or reporting joins with OLTP. That divergence is the entire debate, and it can be measured before anyone writes a review prompt.

Export the result from psql so a later ranker can compute overlap without another catalog round trip.

psql "service=replica_ro" -v ON_ERROR_STOP=1 <<'SQL'
\copy (
  SELECT queryid, calls,
         mean_exec_time AS mean_ms,
         stddev_exec_time AS stddev_ms,
         max_exec_time AS max_ms,
         (mean_exec_time + 2 * stddev_exec_time) AS tail_proxy_ms,
         left(query, 120) AS query_head
  FROM pg_stat_statements
  WHERE calls >= 50
) TO 'pgss_export.csv' WITH CSV HEADER
SQL

A small Python ranker makes the comparison explicit for a review bot. The script is a proposal, not a production service, and it never needs database credentials after the export exists.

# proposal: compare two ranking keys from a CSV export
import csv
from pathlib import Path

def load_rows(path: Path) -> list[dict]:
    with path.open(newline="") as handle:
        return list(csv.DictReader(handle))

def rank(rows: list[dict], key: str, n: int = 10) -> list[dict]:
    scored = sorted(rows, key=lambda r: float(r[key]), reverse=True)
    return scored[:n]

def jaccard(a: list[str], b: list[str]) -> float:
    sa, sb = set(a), set(b)
    return len(sa & sb) / len(sa | sb) if sa | sb else 1.0

rows = load_rows(Path("pgss_export.csv"))
mean_ids = [r["queryid"] for r in rank(rows, "mean_ms")]
tail_ids = [r["queryid"] for r in rank(rows, "tail_proxy_ms")]
print("jaccard_top10", round(jaccard(mean_ids, tail_ids), 3))
print("mean_only", sorted(set(mean_ids) - set(tail_ids)))
print("tail_only", sorted(set(tail_ids) - set(mean_ids)))

If Jaccard on the top ten stays high across a weekday and a weekend snapshot, Position A is enough for that database. If the symmetric difference contains reporting joins or ORM IN lists, Position B is the safer default for the next review window.

True percentiles still need log sampling or auto_explain, because stddev is not p95. Use the proxy to decide which fingerprints deserve a log hunt, not as a substitute for one.

A numbered workflow for the ranking job

1. Confirm shared_preload_libraries includes pg_stat_statements on the instance you will read, then reload if the extension was added only in the config file.
2. Export ranking columns from a replica or a restored snapshot with \copy, never from a primary that is already showing lock waits.
3. Compute Jaccard overlap between mean rank and tail-proxy rank on one weekday file and one weekend file.
4. Feed only queryid, calls, ranking keys, and a redacted query head to the review agent, omitting bind values.
5. Require the agent to label each finding as mean-driven, tail-driven, or insufficient evidence before it proposes an index or rewrite.
6. Block any recommendation that implies EXPLAIN ANALYZE on a primary, or DDL without an explicit lock budget and rollback window.

The ranking job itself does not need to sit beside the database. A detached runtime is useful when several services share one review loop and the export file should leave the primary host.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode offers free model access and a free server option, which can host that detached ranking job and the later review pass without enlarging the production image. The same SQL and Python remain usable on a laptop with a local model if remote review is out of policy. Do not send unredacted SQL text, bind values, or credentials to any remote model, including a free one.

A labeled review prompt, not an executed production prompt, can force the queue policy into the open:

# proposal prompt fragment
You receive a ranked SQL fingerprint list.
Each row has queryid, calls, mean_ms, tail_proxy_ms, cv, max_ms, query_head.
State which ranker selected this row: mean, tail_proxy, or both.
If cv > 1.0 or max_ms / mean_ms > 10, refuse index advice until a replica EXPLAIN exists.
Never recommend EXPLAIN ANALYZE on a writer. Never invent p95 from these columns.

Decision rule

Use Position A when all of the following hold at once. Calls on the top mean queries are high, coefficient of variation stays below 1.0, and on-call has not recorded lock timeouts in the current review window. Use Position B when cv exceeds 1.0, max_exec_time is more than ten times mean_exec_time, or OLTP and ad-hoc reporting share one primary. If pg_stat_statements is absent, stop the agent; do not invent ranks from application logs that lack queryid.

Recompute Jaccard when traffic shape changes, not on a fixed calendar. A review window that follows a release, a reporting deadline, or a vacuum backlog will shift the tail proxy even when mean ranks look unchanged. Store the two ranked lists beside the agent output so later incidents can be traced to the queue, not only to the model.

Limitations and who should not use this

This method does not compute a real p95. Mean plus a multiple of stddev is a Chebyshev-style proxy, and max_exec_time is one observation. Autovacuum, checkpoints, and cold caches inflate tails without proving a bad plan. pg_stat_statements normalizes literals, so two bind shapes that need different indexes can share one queryid and one misleading mean.

Do not use this queue rule on tiny datasets where every query fits in memory and means are mostly noise. Do not treat it as permission for an agent to create indexes unattended. Do not use a remote review server if SQL text cannot be redacted down to a head and a fingerprint. Teams without replica access should export from a restored snapshot, not from the writer under load.

The incident pattern at the start is a reminder that the first query in the prompt is a policy choice. Changing the model without changing the ranker often changes nothing measurable in lock time. If you need a machine that is not the primary for the export-and-review loop, the free server option is one place to park that job.