# Query Optimization

> When to activate: slow query, EXPLAIN ANALYZE, N+1, index, query plan, performance, pagination, query tuning

- Skill: `mattakushi432/query-optimization` (Agent Skill)
- Install (CLI): `npx skillmds@latest add mattakushi432/query-optimization`
- Raw SKILL.md: https://api.skillmd.com/api/skills/mattakushi432/query-optimization/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: Mattakushi432 (https://skillmd.com/u/mattakushi432)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/mattakushi432/query-optimization

---

# Query Optimization Patterns

## EXPLAIN ANALYZE

```sql
-- Full execution stats (PostgreSQL)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

-- Key things to look for:
-- "Seq Scan" on large table → needs index
-- "Nested Loop" with many rows → consider Hash Join
-- High "Buffers: shared hit/read" ratio → cache miss
-- "rows=1000" estimate vs "actual rows=50000" → stale statistics
-- → Run: ANALYZE table_name;

-- MySQL
EXPLAIN FORMAT=JSON SELECT ...;
-- Look for: type (ALL=bad, ref/eq_ref/const=good), key used, rows estimate
```

## N+1 Detection and Fix

```python
# BAD — N+1: 1 query for orders + N queries for each user
orders = Order.query.all()
for o in orders:
    print(o.user.name)  # triggers SELECT each time

# GOOD — eager load with JOIN
orders = Order.query.options(joinedload(Order.user)).all()

# GOOD — subquery load (avoids cartesian product on *-many)
posts = Post.query.options(subqueryload(Post.comments)).all()
```

```sql
-- GOOD — explicit JOIN in SQL, same intent as the eager-load call above
SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending';
```

## Index Selection Strategy

```sql
-- Composite index: equality conditions first, then range/sort
-- Query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY amount DESC
CREATE INDEX idx ON orders (status, created_at, amount DESC);

-- Index-only scan (covering index) — avoid heap fetch
CREATE INDEX idx_cover ON orders (user_id, status) INCLUDE (amount, created_at);

-- Check if index is being used
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes WHERE tablename = 'orders' ORDER BY idx_scan;

-- Unused indexes (drop them — they slow down writes)
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;
```

## Pagination Patterns

```sql
-- BAD: OFFSET is slow on large tables (scans N rows to skip)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

-- GOOD: Keyset/cursor pagination (O(log n) regardless of depth)
-- First page
SELECT * FROM orders WHERE status = 'active' ORDER BY created_at DESC, id DESC LIMIT 20;
-- Next page (use last row's values)
SELECT * FROM orders
WHERE status = 'active'
  AND (created_at, id) < ('2024-01-15 10:00:00', 4521)
ORDER BY created_at DESC, id DESC LIMIT 20;

-- Count approximation (fast alternative to COUNT(*))
SELECT reltuples::BIGINT AS approx_count
FROM pg_class WHERE relname = 'orders';
```

## Query Rewriting

```sql
-- Replace correlated subquery with JOIN
-- BAD
SELECT * FROM orders WHERE user_id IN (
  SELECT id FROM users WHERE country = 'US'
);

-- GOOD
SELECT o.* FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.country = 'US';

-- EXISTS vs IN (EXISTS stops on first match)
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);

-- CTE optimization fence (PostgreSQL — inline vs fence)
-- Force materialization to prevent repeated evaluation:
WITH expensive AS MATERIALIZED (SELECT ...)
SELECT * FROM expensive WHERE ...;
```

## Statistics and Vacuuming

```sql
-- Update statistics manually after bulk load
ANALYZE orders;

-- Increase statistics target for columns with bad estimates
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;

-- Extended statistics for correlated columns
CREATE STATISTICS orders_status_user ON (status, user_id) FROM orders;
ANALYZE orders;

-- Check statistics age
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables ORDER BY last_analyze NULLS FIRST;
```

## Checklist

- [ ] `EXPLAIN ANALYZE` before and after each optimization
- [ ] Indexes on all foreign keys and WHERE/ORDER BY columns
- [ ] Eager-load associations to eliminate N+1
- [ ] Cursor pagination for result sets > 10k rows
- [ ] Statistics current (`ANALYZE` after bulk operations)
- [ ] Query cache via Redis for expensive, infrequently-changing queries