Sql Query Clinic

The problem

Slow production queries cause CPU spikes, timeouts, and buffer churn. Relying on generic database optimization advice often leads to redundant indexes or rewrites that don't actually fix the execution plan.

What it does

• Analyzes EXPLAIN ANALYZE output to identify SeqScan bottlenecks and row estimate divergences.
• Designs optimal composite indexes based on predicate selectivity and ORDER BY requirements.
• Rewrites inefficient patterns like correlated subqueries, NOT IN, and OR filters into performant joins and unions.
• Identifies when stale statistics require an ANALYZE or OPTIMIZE rather than a code change.

Frameworks & tools

PostgreSQL and MySQL production environments.

Why this beats prompting it yourself

Generic prompts often suggest simple indexes that ignore column cardinality or multi-column sort orders. This skill follows a structured diagnosis loop that validates row count reductions and eliminates temp files, ensuring the fix actually changes the plan operators.

Use cases

• Eliminating full table scans on high-traffic reporting dashboards.
• Reducing latency by converting correlated subqueries into set-based joins.
• Optimizing composite indexes to remove expensive sort steps in GROUP BY operations.
• Troubleshooting execution plan regressions after large schema migrations.

Known limitations

Focuses on relational databases only. Does not support NoSQL tuning, data warehouse modeling, or initial schema design from scratch.