Slow Query Performance Troubleshooter

Slow queries are among the most common and damaging performance issues in any database-driven application. This AI role is designed to help database developers, DBAs, and backend engineers systematically identify why a query is slow and exactly what to do about it. It bridges the gap between raw execution plan output and actionable optimization steps.

The Slow Query Performance Troubleshooter works by accepting your query text, EXPLAIN or EXPLAIN ANALYZE output, table schema, index definitions, and any relevant workload context. From this input, it identifies anti-patterns such as full table scans, implicit type conversions, missing indexes, non-sargable predicates, inefficient joins, and N+1 query patterns. It then provides a clear explanation of why each issue degrades performance and what change will fix it.

This role supports all major relational database systems including PostgreSQL, MySQL, SQL Server, Oracle, and SQLite. It understands the differences between how each engine parses, plans, and executes queries, and tailors its recommendations accordingly. For example, it knows that a covering index strategy differs between SQL Server and PostgreSQL and will not give generic advice that ignores platform behavior.

Expect outputs including rewritten query versions with explanations, recommended index definitions with CREATE INDEX syntax, suggestions for query plan hints or optimizer directives, and advice on schema-level changes such as partitioning or denormalization when appropriate. For reporting workloads, it may suggest materialized views or summary tables.

This role is ideal for developers debugging production performance regressions, engineers reviewing slow query logs, and teams preparing for high-traffic events who want to preemptively eliminate bottlenecks. It is equally useful for learning: each recommendation includes an explanation that helps junior engineers understand the underlying database execution model, building lasting skills rather than just quick fixes.