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

4. Pass 3 — Index Analysis For each table in the query, check:

   • Is there an index covering the WHERE clause columns?
   • Is there a covering index for SELECT columns (index-only scan)?
   • Are JOIN columns indexed on both sides?
   • Is there a composite index in the right column order (equality first, then range)?
   • Are there redundant indexes that can be consolidated?

   Generate CREATE INDEX statements:

   -- Composite index for: WHERE user_id = ? AND status = ? ORDER BY created_at DESC
   CREATE INDEX CONCURRENTLY idx_orders_user_status_created
   ON orders (user_id, status, created_at DESC);
   

5. Pass 4 — Engine-Specific Optimizations Apply database-specific patterns:

   PostgreSQL:

   • Use JSONB indexing with GIN for JSON queries
   • Partial indexes for filtered queries
   • BRIN indexes for time-series data
   • LATERAL JOINs for correlated subqueries
   • UNLOGGED tables for ephemeral data

   MySQL:

   • Covering indexes with INCLUDE equivalent
   • FORCE INDEX hints when optimizer chooses wrong plan
   • Partition pruning strategies
   • Query cache considerations

   SQLite:

   • Partial indexes (WHERE clause)
   • WITHOUT ROWID tables for non-integer PKs
   • Expression indexes
   • PRAGMA optimizations (journal_mode, cache_size)

6. Generate the report Structure as:

   Query Optimization Report

   Original Query

   <original query>
   

   Bottlenecks Found

   1. [Severity] Description + line reference

   Optimized Query

   <rewritten query with comments>
   

   Recommended Indexes

   <CREATE INDEX statements>
   

   Expected Impact

   • Scan type change: Seq Scan → Index Scan
   • Estimated row reduction: 500K → 50
   • Index count: +1 new, -0 redundant

Rules

• Always preserve query semantics — the optimized query must return identical results
• Show the original query side-by-side with changes highlighted
• Never add indexes without explaining the write overhead trade-off
• Use CONCURRENTLY for PostgreSQL index creation (no table lock)
• If multiple optimization paths exist, show the best one and mention alternatives
• Consider the table size — an optimization for 1M rows may differ from 1B rows
• Don't assume the user knows EXPLAIN output — annotate the important parts
• If the query is already optimal, say so and explain why
• Support parameterized queries (use placeholders, not literal values)

Example Optimization

-- BEFORE: 2.3s on 1.2M row table
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE plan = 'premium')
AND created_at > NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 20;

-- AFTER: 12ms
SELECT o.id, o.total, o.status, o.created_at
FROM orders o
INNER JOIN users u ON u.id = o.user_id
WHERE u.plan = 'premium'
  AND o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 20;

-- Supporting index
CREATE INDEX CONCURRENTLY idx_orders_user_created
ON orders (user_id, created_at DESC)
WHERE created_at > NOW() - INTERVAL '90 days';