Essential session storage is required to sign in. Optional analytics remain off unless you allow them.
Start from a schema with several slow queries. Use EXPLAIN / EXPLAIN ANALYZE to read the plans, identify the bottlenecks (sequential scans, bad join orders, a function defeating an index), then add the right indexes and rewrite queries to fix them — showing the before/after plan and measured improvement. Also handle one concurrency scenario correctly with an explicit transaction and the appropriate isolation level, and explain a deadlock risk.
Uses EXPLAIN/ANALYZE to identify the real bottleneck (scan vs index, join method, row estimates) — not guesswork.
Adds the right indexes (incl. composite/leftmost-prefix); rewrites queries so the index is actually used; before/after improvement shown.
A concurrency scenario handled with an explicit transaction and a justified isolation level; deadlock risk explained.
Measured timings or plan changes prove the improvement; trade-offs (write cost) acknowledged.
Use this README structure to make the work reviewable.
DevPath fetches a read-only, size-capped snapshot. Submitted code is never executed.
# Diagnose and optimize a slow query workload ## Goal Start from a schema with several slow queries. Use EXPLAIN / EXPLAIN ANALYZE to read the plans, identify the bottlenecks (sequential scans, bad join orders, a function defeating an index), then add the right indexes and rewrite queries to fix them — showing the before/after plan and measured improvement. Also handle one concurrency scenario correctly with an explicit transaction and the appropriate isolation level, and explain a deadlock risk. ## Acceptance criteria - [ ] Plan-driven diagnosis: Uses EXPLAIN/ANALYZE to identify the real bottleneck (scan vs index, join method, row estimates) — not guesswork. - [ ] Effective indexing & rewrites: Adds the right indexes (incl. composite/leftmost-prefix); rewrites queries so the index is actually used; before/after improvement shown. - [ ] Transactions & isolation: A concurrency scenario handled with an explicit transaction and a justified isolation level; deadlock risk explained. - [ ] Evidence: Measured timings or plan changes prove the improvement; trade-offs (write cost) acknowledged. ## Verification - [ ] Document installation and run commands. - [ ] Record automated checks and their exact commands. - [ ] Add representative output, screenshots, or a short demo where useful. - [ ] Confirm failure paths and known constraints. ## Decision log ### Decision title - Context: - Choice: - Alternatives considered: - Tradeoffs: ## Evidence - Link each rubric criterion to the file, test, or artifact that demonstrates it. ## Known limitations - List what is intentionally out of scope and what should be improved next.