sql-query-optimizer

SQL Query Optimizer

Analyze SQL queries for performance bottlenecks and produce optimized variants with index suggestions and execution plan interpretations.

Workflow

1. Collect context Identify:

   • The SQL query (provided directly or found in source code)
   • The database engine: PostgreSQL (default), MySQL/MariaDB, or SQLite
   • Whether the user has an EXPLAIN/EXPLAIN ANALYZE output
   • Table schemas (CREATE TABLE statements or ORM models)
   • Approximate table sizes (millions of rows? thousands?)
   • Current indexes on relevant tables

2. Pass 1 — Execution Plan Analysis If EXPLAIN output is provided, interpret it:

   • Seq Scans on large tables — Missing index
   • Nested Loop with high row estimates — Cartesian product risk
   • Sort with high cost — Missing index for ORDER BY
   • Hash Join on small tables — Usually fine
   • Bitmap Heap Scan — Good for moderate selectivity
   • Materialize — Subquery not flattened
   • Buffers: shared read > hit — Poor cache utilization

   If no EXPLAIN is provided, generate one:

   -- PostgreSQL
   EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) <query>;
   
   -- MySQL
   EXPLAIN ANALYZE <query>;
   

3. Pass 2 — Query Rewrite Analysis Check for common anti-patterns:

   Anti-Pattern	Fix
   SELECT *	Select only needed columns
   Subquery in WHERE (correlated)	Rewrite as JOIN or EXISTS
   OR across different columns	UNION ALL or separate queries
   Function on indexed column	Expression index or computed column
   LIKE '%prefix'	Full-text index or trigram index
   Implicit type cast	Match types to avoid cast on indexed col
   NOT IN with nullable column	Rewrite as NOT EXISTS
   COUNT(*) on large table	Use count estimate or cached counter
   DISTINCT without reason	Check if duplicates are actually possible
   OFFSET for pagination	Keyset pagination (cursor-based)
   CTE in PostgreSQL < 12	Materialized — may prevent optimization
   HAVING without GROUP BY	Move to WHERE