query-optimization

What problem does it solve? Slow database queries are hard to fix without a systematic method. This Skill provides a structured diagnostic workflow for investigating slow queries, reading execution plans, and designing justified indexes, so changes are measured and validated instead of guessed. ## Core Features & Use Cases - Diagnostic workflow: Capture a baseline (p50/p95 latency, rows examined), obtain the plan with EXPLAIN (ANALYZE, BUFFERS), identify common causes like sequential scans, stale statistics, and nested loop misuse, then propose the smallest fix. - Index design heuristics: Apply the ESR rule (equality, sort, range), covering indexes with INCLUDE columns, and partial indexes, while justifying each index against its write cost. - Antipattern detection: Flag SELECT * in critical paths, functions on indexed columns in WHERE clauses, and ORM N+1 problems. - Use Case: A PostgreSQL endpoint is slow. You paste the EXPLAIN ANALYZE output, and the Skill identifies a sequential scan on a large table with a selective predicate, proposes a CREATE INDEX CONCURRENTLY statement, and provides a validation checklist. ## Quick Start Ask the assistant to analyze why this query is slow using the attached EXPLAIN ANALYZE output and propose the smallest fix.