Query Optimization Catalog

Query Optimization Catalog — SQL Query Optimization Catalog

A reference of index strategies, execution plan analysis, and query anti-pattern resolution used by the performance-analyst agent during performance optimization.

Target Agent

performance-analyst — Directly applies the optimization techniques from this skill to performance analysis and index design.

Index Strategies

Index Type Selection Guide

Composite Index Design Principles

Leftmost Prefix Rule

INDEX idx_abc ON table(a, b, c)

-- Usable:
WHERE a = 1                          -- O (a only)
WHERE a = 1 AND b = 2               -- O (a, b)
WHERE a = 1 AND b = 2 AND c = 3     -- O (all columns)
WHERE a = 1 AND c = 3               -- Partial (a only, c skipped)

-- Not usable:
WHERE b = 2                          -- X (a missing)
WHERE c = 3                          -- X (a, b missing)

Column Order Decision Rules

1. WHERE equality (=) condition columns first
2. Sort (ORDER BY) columns next
3. Range (<, >, BETWEEN) columns last
4. Higher cardinality first (but rules 1-3 take priority)

Covering Indexes

Return query results using only the index (no table access needed).

-- Covering index
CREATE INDEX idx_covering ON orders(user_id, status, created_at);

-- This query responds from the index only (Index Only Scan)
SELECT status, created_at FROM orders WHERE user_id = 123;

Index Add/Remove Decision Guide

Slow Query Anti-Patterns & Solutions

1. N+1 Problem

-- Anti-pattern: Individual queries in a loop
SELECT * FROM users;
-- For each user:
SELECT * FROM orders WHERE user_id = ?;  -- Repeated N times!

-- Solution: JOIN or IN
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Or batch loading
SELECT * FROM orders WHERE user_id IN (1, 2, 3, ...);

2. SELECT *

-- Anti-pattern
SELECT * FROM products WHERE category = 'electronics';

-- Solution: Only needed columns
SELECT id, name, price FROM products WHERE category = 'electronics';
-- Enables covering index usage

3. Function Invalidating Index

-- Anti-pattern: Applying function to indexed column
WHERE YEAR(created_at) = 2025

-- Solution: Convert to range condition
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'

4. OR Condition Invalidating Index

-- Anti-pattern
WHERE status = 'active' OR category = 'books'

-- Solution: UNION ALL
SELECT * FROM products WHERE status = 'active'
UNION ALL
SELECT * FROM products WHERE category = 'books' AND status != 'active'

5. Subquery vs JOIN

-- Anti-pattern: Correlated subquery
SELECT * FROM orders o
WHERE o.total > (SELECT AVG(total) FROM orders WHERE user_id = o.user_id);

-- Solution: JOIN + aggregate
SELECT o.* FROM orders o
JOIN (SELECT user_id, AVG(total) as avg_total FROM orders GROUP BY user_id) a
ON o.user_id = a.user_id
WHERE o.total > a.avg_total;

6. OFFSET Pagination

-- Anti-pattern: Deep OFFSET
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 100000;

-- Solution: Cursor-based (Keyset)
SELECT * FROM products WHERE id > 100000 ORDER BY id LIMIT 20;

7. Large IN Clause

-- Anti-pattern: Thousands of IDs
WHERE id IN (1, 2, 3, ..., 10000)

-- Solution: Temporary table or JOIN
-- PostgreSQL: VALUES or ANY(ARRAY[...])
-- General: Batch processing (500 at a time)

EXPLAIN Analysis Guide (PostgreSQL)

Reading Execution Plans

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123 AND status = 'completed';

Key Node Types

Warning Signs

• Seq Scan on a large table -> Index needed
• Sort with external merge Disk -> Insufficient work_mem
• actual rows >> estimated rows -> Stale statistics (ANALYZE needed)
• Nested Loop with a large table -> Encourage Hash Join

Partitioning Strategy

Signs Partitioning Is Needed

• Table size > hundreds of millions of rows
• Time-series data (logs, events, metrics)
• Periodic deletion/archiving of old data
• Most queries target specific time ranges

Partitioning Types

Range Partitioning Example (PostgreSQL)

CREATE TABLE events (
  id BIGINT,
  event_time TIMESTAMPTZ,
  data JSONB
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2025_q1 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

Caching Strategy

Cache Invalidation Strategies

• TTL-based: Auto-refresh after expiration time
• Event-based: Immediate deletion on data change
• Write-Through: Update cache on writes
• Cache-Aside: On read miss, query DB and store in cache