Optimize Database Query Performance

Query Performance Tuner is a skill for database performance tuning across PostgreSQL, MySQL, SQL Server, and Oracle, covering execution-plan analysis, index design, and query rewriting. It applies a systematic analysis framework: examine execution-plan operator costs and row estimates, check for missing/unused/suboptimal indexes, evaluate join order and algorithm choice, verify filters are pushed down early, and confirm table/column statistics are current. It flags specific execution-plan red flags: table scans on large tables (over 100k rows), key lookups with high estimated row counts, hash joins on large datasets without proper indexes, sort operations consuming excessive memory, and parameter-sniffing issues that cause a cached plan to be reused for a mismatched workload.

Use it when a specific query or workload is slow and you have (or can get) an actual execution plan and table schema - the skill explicitly asks for these before giving analysis, since they're needed for accurate diagnosis. It covers composite-index column ordering, e.g.:

-- BAD: Wrong column order
CREATE INDEX idx_bad ON orders (created_date, customer_id, status);

-- GOOD: Selective columns first, range columns last
CREATE INDEX idx_good ON orders (status, customer_id, created_date);

-- Query benefits from proper ordering
SELECT * FROM orders 
WHERE status = 'active' 
  AND customer_id = 12345 
  AND created_date >= '2024-01-01';

and covering indexes that add non-key columns via INCLUDE to avoid extra key lookups. It is not meant for schema design from scratch or for query correctness/logic review - it is scoped to making existing, already-correct queries faster.

Input is a query, its execution plan, and relevant schema/index definitions. Output is a set of concrete rewrites and index recommendations: converting a correlated EXISTS subquery to an INNER JOIN with DISTINCT, consolidating repeated OVER (PARTITION BY ... ORDER BY ...) clauses into a single named WINDOW specification, replacing UNION with UNION ALL when duplicates are known not to occur, and rewriting non-sargable predicates like WHERE YEAR(order_date) = 2024 into a range predicate (order_date >= '2024-01-01' AND order_date < '2025-01-01') so the optimizer can still use an index on the column.

For PostgreSQL specifically it recommends EXPLAIN (ANALYZE, BUFFERS, TIMING) for detailed plan output, a pg_stat_user_tables query to surface tables with high sequential-scan counts, ANALYZE to refresh statistics, and pg_stat_statements to identify the most expensive queries by total and mean execution time. For SQL Server it recommends UPDATE STATISTICS ... WITH FULLSCAN. It also covers partition pruning (keeping the partition key in the WHERE clause) as an advanced technique for partitioned tables.

Database engineers, backend developers, and DBAs diagnosing slow queries who want a structured tuning workflow rather than ad hoc trial and error. When delivering recommendations, the skill's own protocol is to quantify expected performance impact, prioritize changes by effort-versus-impact, include monitoring queries to measure the improvement, call out trade-offs like extra storage or index-maintenance cost, and provide a rollback plan for every change.