database-query-optimizer_skill

Overview

This skill analyzes and optimizes database queries for PostgreSQL, MySQL, and MongoDB. It interprets EXPLAIN/analysis output, detects N+1 patterns, and suggests index and query rewrites to reduce cost and latency. Use it to get actionable fixes, index recommendations, and ORM-specific guidance.

How this skill works

It parses EXPLAIN/EXPLAIN ANALYZE output and executionStats to highlight expensive nodes, sequential scans, misestimated row counts, and high loop counts. It inspects SQL or ORM code for anti-patterns (SELECT *, functions on indexed columns, OR-heavy filters, subqueries causing repeated queries) and returns index suggestions, rewritten query examples, and eager-loading fixes for ORMs. For MongoDB it reads explain("executionStats") output and recommends compound or partial indexes.

When to use it

• When you ask to "optimize query" or "fix slow queries"
• When you want an interpretation of an EXPLAIN/EXPLAIN ANALYZE plan
• When you need index suggestions for Postgres, MySQL, or MongoDB
• When you suspect N+1 queries in ORM code (Django, Rails, SQLAlchemy, TypeORM, Sequelize)
• When you need concrete query rewrites or covering/index examples

Best practices

• Run EXPLAIN ANALYZE (or explain("executionStats") for MongoDB) before changes to collect real metrics
• Add indexes on join and filter columns, prefer composite/partial indexes for common predicates
• Avoid SELECT *; select only required columns or use covering indexes
• Eliminate functions on indexed columns or create functional indexes where needed
• Use eager loading (join/select_related/prefetch) to fix N+1 issues and use pagination/iterators for large scans
• Monitor pg_stat_statements or MySQL slow log and review unused or duplicate indexes regularly

Example use cases

• Analyze a slow Postgres query: interpret costs, identify sequential scans, and propose CREATE INDEX statements and query rewrites
• Detect and fix N+1 queries in Django, Rails, SQLAlchemy, or TypeORM by recommending select_related/prefetch/includes or join strategies
• Review MongoDB explain output, flag COLLSCAN, and recommend compound indexes matching sort and filter order
• Suggest composite or partial indexes for frequent filters and ORDER BY patterns to enable index-only scans
• Identify expensive aggregates and propose rewritten aggregation, pre-aggregation, or materialized view strategies