Analyze and Optimize SQL Queries

This skill is an expert SQL performance analyst and database optimization specialist with deep knowledge of query execution plans, indexing strategies, and engine internals across MySQL, PostgreSQL, SQL Server, and Oracle. Its performance assessment methodology covers five steps: execution plan analysis (operators, costs, cardinality estimates), index usage evaluation (missing, unused, or suboptimal indexes), join strategy assessment, predicate/WHERE-clause selectivity analysis, and resource consumption evaluation. It identifies critical performance indicators - table scans and nested loop joins on large tables, cardinality misestimates between actual and estimated row counts, blocking operations like sorts and hash builds, and suboptimal index scans versus seeks. It fixes common anti-patterns with before/after SQL: the N+1 query problem solved with a JOIN instead of repeated per-row queries, correlated EXISTS subqueries replaced with equivalent INNER JOINs, and non-sargable function-wrapped WHERE clauses (like YEAR(order_date)=2023) rewritten as range comparisons that stay index-friendly.

Use this skill when diagnosing slow queries or designing indexes that need real execution-plan-level analysis, not guesswork. It covers composite/covering index design matched to a specific query's filter and sort columns, index selectivity measurement via a distinct-value-ratio query, join optimization (forcing a hash join algorithm when needed, ensuring join predicates use compatible data types to avoid implicit conversion), and window function optimization that consolidates redundant partitions into a single CTE-based pass. It names concrete plan-operator red flags (Key Lookups indicating a need for covering indexes, Sort operations suggesting indexed ORDER BY access, Hash Match warnings indicating tempdb spill, high-outer-input Nested Loops needing a hash/merge join) and gives platform-specific tooling: PostgreSQL's EXPLAIN ANALYZE BUFFERS and partial indexes, SQL Server's Query Store and dm_exec_query_stats DMVs. It is not a general SQL-writing tutorial - it's scoped specifically to diagnosing and fixing performance problems in already-working queries.

-- Query pattern
SELECT order_id, total_amount
FROM orders 
WHERE customer_id = 123 
AND order_date >= '2023-01-01'
AND status = 'completed'
ORDER BY order_date DESC;

-- Optimal covering index
CREATE INDEX IX_orders_covering 
ON orders (customer_id, status, order_date DESC, order_id, total_amount);

Given a slow query or execution plan, the skill produces a structured recommendation set: immediate critical issues, specific CREATE INDEX statements with rationale like the one above, query rewrites with expected performance gains, configuration tuning suggestions, post-optimization monitoring metrics, and a risk assessment of proposed changes - always quantifying expected improvement and comparing before/after execution plans where possible.

Database administrators and backend engineers diagnosing slow queries or designing indexes who need execution-plan-level analysis and platform-specific tooling (PostgreSQL EXPLAIN ANALYZE, SQL Server Query Store) rather than generic SQL advice. It suits teams that want a repeatable optimization framework - baseline capture, anti-pattern identification, indexed rewrite, and quantified before/after comparison - for queries that are already correct but too slow.