/optimizing-sql

name: optimizing-sql
description: Optimize SQL query performance through EXPLAIN analysis, indexing strategies, and query rewriting for PostgreSQL, MySQL, and SQL Server. Use when debugging slow queries, analyzing execution plans, or improving database performance.

SQL Optimization

Provide tactical guidance for optimizing SQL query performance across PostgreSQL, MySQL, and SQL Server through execution plan analysis, strategic indexing, and query rewriting.

When to Use This Skill

Trigger this skill when encountering:

• Slow query performance or database timeouts
• Analyzing EXPLAIN plans or execution plans
• Determining index requirements
• Rewriting inefficient queries
• Identifying query anti-patterns (N+1, SELECT *, correlated subqueries)
• Database-specific optimization needs (PostgreSQL, MySQL, SQL Server)

Core Optimization Workflow

Step 1: Analyze Query Performance

Run execution plan analysis to identify bottlenecks:

**PostgreSQL:**

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

**MySQL:**

EXPLAIN FORMAT=JSON SELECT * FROM products WHERE category_id = 5;

**SQL Server:** Use SQL Server Management Studio: Display Estimated Execution Plan (Ctrl+L)

**Key Metrics to Monitor:**

• **Cost**: Estimated resource consumption
• **Rows**: Number of rows processed (estimated vs actual)
• **Scan Type**: Sequential scan vs index scan
• **Execution Time**: Actual time spent on operation

For detailed execution plan interpretation, see `references/explain-guide.md`.

Step 2: Identify Optimization Opportunities

**Common Red Flags:**

| Indicator | Problem | Solution | |-----------|---------|----------| | Seq Scan / Table Scan | Full table scan on large table | Add index on filter columns | | High row count | Processing excessive rows | Add WHERE filter or index | | Nested Loop with large outer table | Inefficient join algorithm | Index join columns | | Correlated subquery | Subquery executes per row | Rewrite as JOIN or EXISTS | | Sort operation on large result set | Expensive sorting | Add index matching ORDER BY |

For scan type interpretation, see `references/scan-types.md`.

Step 3: Apply Indexing Strategies

**Index Decision Framework:**

Is column used in WHERE, JOIN, ORDER BY, or GROUP BY?
├─ YES → Is column selective (many unique values)?
│  ├─ YES → Is table frequently queried?
│  │  ├─ YES → ADD INDEX
│  │  └─ NO → Consider based on query frequency
│  └─ NO (low selectivity) → Skip index
└─ NO → Skip index

**Index Types by Use Case:**

**PostgreSQL:**

• **B-tree** (default): General-purpose, supports <, ≤, =, ≥, >, BETWEEN, IN
• **Hash**: Equality comparisons only (=)
• **GIN**: Full-text search, JSONB, arrays
• **GiST**: Spatial data, geometric types
• **BRIN**: Very large tables with naturally ordered data

**MySQL:**

• **B-tree** (default): General-purpose index
• **Full-text**: Text search on VARCHAR/TEXT columns
• **Spatial**: Spatial data types

**SQL Server:**

• **Clustered**: Table data sorted by index (one per table)
• **Non-clustered**: Separate index structure (multiple allowed)

For comprehensive indexing guidance, see `references/indexing-decisions.md` and `references/index-types.md`.

Step 4: Design Composite Indexes

For queries filtering on multiple columns, use composite indexes:

**Column Order Matters:** 1. **Equality filters first** (most selective) 2. **Additional equality filters** (by selectivity) 3. **Range filters or ORDER BY** (last)

**Example:**

-- Query pattern
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 10;

-- Optimal composite index
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

For composite index design patterns, see `references/composite-indexes.md`.

Step 5: Rewrite Inefficient Queries

**Common Anti-Patterns to Avoid:**

**1. SELECT * (Over-fetching)**

-- ❌ Bad: Fetches all columns
SELECT * FROM users WHERE id = 1;

-- ✅ Good: Fetch only needed columns
SELECT id, name, email FROM users WHERE id = 1;

**2. N+1 Queries**

-- ❌ Bad: 1 + N queries
SELECT * FROM users LIMIT 100;
-- Then in loop: SELECT * FROM posts WHERE user_id = ?;

-- ✅ Good: Single JOIN
SELECT users.*, posts.id AS post_id, posts.title
FROM users
LEFT JOIN posts ON users.id = posts.user_id;

**3. Non-Sargable Queries** (functions on indexed columns)

-- ❌ Bad: Function prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

-- ✅ Good: Sargable range condition
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

**4. Correlated Subqueries**

-- ❌ Bad: Subquery executes per row
SELECT name,
  (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id)
FROM users;

-- ✅ Good: JOIN with GROUP BY
SELECT users.name, COUNT(orders.id) AS order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

For complete anti-pattern reference, see `references/anti-patterns.md`. For efficient query patterns, see `references/efficient-patterns.md`.

Quick Reference Tables

Index Selection Guide

| Query Pattern | Index Type | Example | |--------------|------------|---------| | `WHERE column = value` | Single-column B-tree | `CREATE INDEX ON table (column)` | | `WHERE col1 = ? AND col2 = ?` | Composite B-tree | `CREATE INDEX ON table (col1, col2)` | | `WHERE text_col LIKE '%word%'` | Full-text (GIN/Full-text) | `CREATE INDEX ON table USING GIN (to_tsvector('english', text_col))` | | `WHERE geom && box` | Spatial (GiST) | `CREATE INDEX ON table USING GIST (geom)` | | `WHERE json_col @> '{"key":"value"}'` | JSONB (GIN) | `CREATE INDEX ON table USING GIN (json_col)` |

Join Optimization Checklist

• [ ] Index foreign key columns on both sides of JOIN
• [ ] Order joins starting with table returning fewest rows
• [ ] Use INNER JOIN when possible (more effici