Postgres proof lab

A slow-query recommendation that shows its work.

This public packet is the artifact to put in front of serious Postgres reviewers: query fingerprint, baseline plan, shadow index proof, lock posture, rollback SQL, and the reasons QueryRook might still be wrong.

Send critique ->Read daily notes Test your workload

QR-LAB-2026-001

recommendation only

Group the workload before tuning

QueryRook starts from a normalized pg_stat_statements fingerprint, not one pasted query. The packet keeps call volume, latency, rows, and tenant scope beside the SQL.

b8f14f2a7c6d

Capture the current execution shape

The current plan is a parallel scan over a large event table. That is not automatically bad, but it is suspicious for a tenant-scoped 30-day timeline query.

Parallel Seq Scan

Test the candidate without touching production

The candidate index is evaluated as a hypothetical plan first. QueryRook treats the cost delta as evidence for review, not as permission to mutate the database.

-86.6%

Attach lock, write, and rollback posture

The packet is incomplete until it names lock behavior, storage pressure, write amplification, cancellation risk, and how to back out if the recommendation is wrong.

human approve

-86.6%

planner cost

2.43s

lab p95

auto-applied DDL

Query fingerprint

The packet starts with the workload, not the fix.

Fingerprint: b8f14f2a7c6d
Calls: 18,420 calls / 24h replay
Workload: Synthetic SaaS activity timeline fixture

SELECT date_trunc('day', created_at) AS bucket,
       count(*) AS events
FROM app_events
WHERE tenant_id = $1
  AND created_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;

Plan comparison

The recommendation survives only if the evidence is inspectable.

QueryRook separates the baseline plan from the hypothetical candidate. A good reviewer should be able to disagree with either side of the packet.

current plan

Finalize GroupAggregate
  Group Key: date_trunc('day', created_at)
  -> Gather Merge
       Workers Planned: 2
       -> Partial HashAggregate
            -> Parallel Seq Scan on app_events
                 Filter: tenant_id = $1
                         AND created_at >= now() - '30 days'

Rows in table: 14.8M
Rows after filter: 31,204
Planner cost: 582,913.24
Lab replay p95: 2.43s

shadow candidate

GroupAggregate
  Group Key: date_trunc('day', created_at)
  -> Bitmap Heap Scan on app_events
       Recheck Cond: tenant_id = $1
                     AND created_at >= now() - '30 days'
       -> Bitmap Index Scan on hypopg_idx_events_tenant_created_at
            Index Cond: tenant_id = $1
                        AND created_at >= now() - '30 days'

Hypothetical index: (tenant_id, created_at DESC)
Planner cost: 78,244.39
Planner-cost delta: -86.6%

Evidence contract

What the packet actually claims.

The lab proof supports a recommendation for review. It does not claim QueryRook should execute DDL, and it does not claim the same index is correct for every tenant.

Source

pg_stat_statements plus EXPLAIN from a synthetic lab fixture

Scope

Tenant-scoped timeline query on app_events

Candidate

CREATE INDEX CONCURRENTLY idx_events_tenant_created_at ON app_events (tenant_id, created_at DESC)

Expected benefit

Planner switches from broad parallel scan to index-assisted filtered heap access

Promotion state

Recommendation stays blocked until a human reviews the packet

Lock posture

CREATE INDEX CONCURRENTLY reduces blocking but is not free. The packet calls out lock phases, cancellation behavior, and invalid-index cleanup before any approval.

Rollback posture

The rollback contract is explicit: DROP INDEX CONCURRENTLY IF EXISTS idx_events_tenant_created_at, followed by plan and latency checks.

Human gate

The evidence packet can be exported, critiqued, and approved. It cannot approve itself or run DDL on a production target.

Ways this could be wrong

Planner-cost improvement is not the same as production runtime improvement.
The candidate can increase write cost on a high-ingest event table.
CREATE INDEX CONCURRENTLY still has lock phases and can leave an invalid index if interrupted.
A BRIN index or partitioning strategy may be better for some append-heavy timelines.

Export-shaped artifact

{
  "packet_id": "QR-LAB-2026-001",
  "action": "recommend_index",
  "table": "app_events",
  "candidate_sql": "CREATE INDEX CONCURRENTLY idx_events_tenant_created_at ON app_events (tenant_id, created_at DESC)",
  "proof_state": "shadow_plan_only",
  "human_gate": "required",
  "rollback_sql": "DROP INDEX CONCURRENTLY IF EXISTS idx_events_tenant_created_at",
  "caveat": "Lab proof does not guarantee production runtime improvement."
}

Invite hard criticism

This is the page to send to Postgres reviewers.

The right outreach is not asking reviewers to admire the product. It is asking them to tell us where this proof packet overclaims. These are the questions worth asking in public.

review

Is the candidate index too narrow or too eager for this workload?

review

Would BRIN, partition pruning, or a partial index be the better first experiment?

review

What signal is missing before this should reach a production change review?

Send critique ->Run a proof pilot