Capture buffer reads and row estimates for the slow inbox query
The ticket says the query takes seconds but contains no plan, parameters or indication of whether the cache was warm.
- Focused work estimate
- 1h 30m + prerequisites
- Priority in the scenario
- Medium
- Engineering practice
- Execution plans · Measurement
Estimated field mix
- Database engineering50%
- Performance engineering50%
Field percentages are editorial estimates of the ticket's engineering focus. They total 100%; they are not measured time, proficiency scores, or ownership evidence.
Review it, then add it to your workspace.
The board opens an editable draft; nothing is saved until you confirm it. Sign-in and workspace permissions apply, and Demo boards remain ephemeral.
Project context
A fictional maintenance service lists open work orders beside years of closed history. A query that was cheap in a small demo now scans far more rows than it returns. Recreate the schema and synthetic workload locally; no customer database or supplied fixture is assumed.
Setup prerequisites
- Create an isolated local database with tenants, work orders and assignment history.
- Seed 200,000 synthetic work orders with documented skew and a repeatable random seed; record PostgreSQL version, settings and resource limits.
Preceding work
Complete these dependencies, or supply their agreed outputs before taking this ticket.
Acceptance criteria
- Save EXPLAIN ANALYZE with buffers for the large, median and empty tenant fixtures.
- Record query parameters, statistics freshness, cache-warmup procedure and database resource settings.
- Identify estimate-versus-actual row differences and time-consuming nodes without claiming a fix from plan shape alone.
Implementation constraints
- Use SELECT-only plans here; running ANALYZE on mutating statements would execute them.
Verification to include
- Repeat each parameter case three times after the declared warmup and report all durations.
- Verify the captured query returns the fixture IDs from the baseline contract.
Deliverables
- Annotated plans and reproducible capture command
Rollout and recovery
Keep the baseline artifacts unchanged when evaluating candidate queries; new fixture revisions get new run labels.
Value of the work
For the engineer: Practice reading execution plans, balancing read and write cost, and validating performance without weakening tenant boundaries.
For the team: Create reproducible query diagnostics and reversible index or query proposals for an operational application.
Evidence boundaries
Outcome Evidence: Tests, patches, and runbooks are requested deliverables. They become Outcome Evidence only through a qualified Mission and immutable Evidence IDs.
Ownership Evidence: Independent adaptation must be observed under a declared verification policy and cite immutable Evidence IDs. Completing a planning ticket establishes no Ownership Evidence.