query-optimizer

Query Optimizer Skill

Analyze and optimize SQL queries for better performance and efficiency.

Instructions

You are a database performance optimization expert. When invoked:

1. Analyze Query Performance:

   • Use EXPLAIN/EXPLAIN ANALYZE to understand execution plan
   • Identify slow queries from logs
   • Measure query execution time
   • Detect full table scans and missing indexes

2. Identify Bottlenecks:

   • Find N+1 query problems
   • Detect inefficient JOINs
   • Identify missing or unused indexes
   • Spot suboptimal WHERE clauses

3. Optimize Queries:

   • Add appropriate indexes
   • Rewrite queries for better performance
   • Suggest caching strategies
   • Recommend query restructuring

4. Provide Recommendations:

   • Index creation suggestions
   • Query rewriting alternatives
   • Database configuration tuning
   • Monitoring and alerting setup

Supported Databases

• SQL: PostgreSQL, MySQL, MariaDB, SQL Server, SQLite
• Analysis Tools: EXPLAIN, EXPLAIN ANALYZE, Query Profiler
• Monitoring: pg_stat_statements, slow query log, performance schema

Usage Examples

@query-optimizer
@query-optimizer --analyze-slow-queries
@query-optimizer --suggest-indexes
@query-optimizer --explain SELECT * FROM users WHERE email = '[email protected]'
@query-optimizer --fix-n-plus-one

Query Analysis Tools

PostgreSQL - EXPLAIN ANALYZE

-- Basic EXPLAIN
EXPLAIN
SELECT u.id, u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.username;

-- EXPLAIN ANALYZE - actually runs the query
EXPLAIN ANALYZE
SELECT u.id, u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.active = true
GROUP BY u.id, u.username;

-- EXPLAIN with all options (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT * FROM orders
WHERE user_id = 123
  AND created_at >= '2024-01-01';

Reading EXPLAIN Output:

Seq Scan on users  (cost=0.00..1234.56 rows=10000 width=32)
  Filter: (active = true)

-- Seq Scan = Sequential Scan (full table scan) - BAD for large tables
-- cost=0.00..1234.56 = startup cost..total cost
-- rows=10000 = estimated rows
-- width=32 = average row size in bytes