Essential session storage is required to sign in. Optional analytics remain off unless you allow them.
Set up a PostgreSQL database with a table of at least a few hundred thousand rows (generate_series works). Find a query that runs slowly, then use EXPLAIN ANALYZE to read its plan and identify why (e.g. a Sequential Scan on a filtered column, a bad row estimate, an expensive sort). Add the right index (B-tree, partial, or expression as appropriate), re-run EXPLAIN ANALYZE, and document the before/after plans and timings. Explain WHY the index changed the plan, and note the write-side cost of the index.
Correctly reads EXPLAIN ANALYZE (estimated vs actual rows, scan/join nodes); identifies the real cause of slowness, not a guess.
Right index type for the query (B-tree/partial/expression); plan changes to an index/index-only scan; measurable speedup shown.
Explains why the planner switched plans and acknowledges the index's write/space cost and that ANALYZE keeps stats fresh.
Before/after plans and timings captured; reproducible setup.
Use this README structure to make the work reviewable.
DevPath fetches a read-only, size-capped snapshot. Submitted code is never executed.
# Diagnose and fix a slow query with EXPLAIN ## Goal Set up a PostgreSQL database with a table of at least a few hundred thousand rows (generate_series works). Find a query that runs slowly, then use EXPLAIN ANALYZE to read its plan and identify why (e.g. a Sequential Scan on a filtered column, a bad row estimate, an expensive sort). Add the right index (B-tree, partial, or expression as appropriate), re-run EXPLAIN ANALYZE, and document the before/after plans and timings. Explain WHY the index changed the plan, and note the write-side cost of the index. ## Acceptance criteria - [ ] Diagnosis: Correctly reads EXPLAIN ANALYZE (estimated vs actual rows, scan/join nodes); identifies the real cause of slowness, not a guess. - [ ] Correct fix: Right index type for the query (B-tree/partial/expression); plan changes to an index/index-only scan; measurable speedup shown. - [ ] Understanding: Explains why the planner switched plans and acknowledges the index's write/space cost and that ANALYZE keeps stats fresh. - [ ] Rigor: Before/after plans and timings captured; reproducible setup. ## 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.