SQL Optimization Patterns

Seth HobsonCommunityCreated by Seth Hobson

Skill
Guidelines
Software development
MIT
No issues found

From the author

Master SQL query optimization, indexing strategies, and EXPLAIN analysis to dramatically improve database performance and eliminate slow queries. Use when debugging slow queries, designing database schemas, or optimizing application performance.

— the description by Seth Hobson

AutoGineer summary

Written by AutoGineer from the skill’s own files, not by the author

This skill provides a systematic approach to identifying and resolving database performance bottlenecks. It covers the interpretation of execution plans, strategic indexing techniques, and common query anti-patterns. By applying these guidelines, developers can reduce database load, improve application response times, and ensure scalability for growing datasets.

What it does

Explains how to use EXPLAIN and EXPLAIN ANALYZE to diagnose slow queries.
Details various indexing strategies including B-Tree, GIN, and partial indexes.
Provides patterns for optimizing JOINs, subqueries, and aggregation operations.
Offers techniques for efficient pagination and batch processing.
Identifies common pitfalls like N+1 queries and implicit type conversions.
Includes maintenance best practices such as updating statistics and index management.

When to use it

When debugging slow-running database queries.
During the design phase of database schemas to ensure future performance.
When optimizing application response times for high-traffic systems.
When reducing database infrastructure costs and load.
When resolving N+1 query problems in application code.

Advantages

Reduces database latency and improves overall application performance.
Provides actionable patterns for common SQL performance issues.
Helps prevent common anti-patterns that lead to slow database operations.
Supports better resource utilization through efficient indexing and query design.

Usage examples

“How can I optimize a slow query that uses OFFSET for pagination?”: The skill provides a pattern for cursor-based pagination, replacing slow OFFSET operations with a WHERE clause filter on the last seen ID or timestamp, significantly improving performance on large tables.
“My query is performing a full table scan, how do I fix it?”: The skill guides you through using EXPLAIN to confirm the scan type and suggests appropriate indexing strategies, such as B-Tree or composite indexes, to enable index scans instead.
“How do I avoid N+1 queries when fetching users and their orders?”: The skill demonstrates how to refactor code to use JOINs or batch loading, allowing you to retrieve all necessary data in a single query rather than executing a query for every user.
“What is the best way to count rows in a large table without slowing down the database?”: The skill suggests using database statistics for estimates or filtering by indexed columns to perform an index-only scan, avoiding the performance hit of a full table count.

Install

Download the skill as a ZIP:
https://mp-staging.autogineer.com/agent-skills/wshobson/sql-optimization-patterns/download

Unzip it into the cross-client skills folder of your project:

mkdir -p .agents/skills && unzip sql-optimization-patterns.zip -d .agents/skills

Documentation by Seth Hobson

SQL Optimization Patterns

Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.

When to Use This Skill

Debugging slow-running queries
Designing performant database schemas
Optimizing application response times
Reducing database load and costs
Improving scalability for growing datasets
Analyzing EXPLAIN query plans
Implementing efficient indexes
Resolving N+1 query problems

Core Concepts

1. Query Execution Plans (EXPLAIN)

Understanding EXPLAIN output is fundamental to optimization.

PostgreSQL EXPLAIN:

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- With actual execution stats
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

-- Verbose output with more details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';

Key Metrics to Watch:

Seq Scan: Full table scan (usually slow for large tables)
Index Scan: Using index (good)
Index Only Scan: Using index without touching table (best)
Nested Loop: Join method (okay for small datasets)
Hash Join: Join method (good for larger datasets)
Merge Join: Join method (good for sorted data)
Cost: Estimated query cost (lower is better)
Rows: Estimated rows returned
Actual Time: Real execution time

2. Index Strategies

Indexes are the most powerful optimization tool.

Index Types:

B-Tree: Default, good for equality and range queries
Hash: Only for equality (=) comparisons
GIN: Full-text search, array queries, JSONB
GiST: Geometric data, full-text search
BRIN: Block Range INdex for very large tables with correlation

-- Standard B-Tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index (index subset of rows)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Covering index (include additional columns)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);

-- Full-text search index
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || body));

-- JSONB index
CREATE INDEX idx_metadata ON events USING GIN(metadata);

3. Query Optimization Patterns

Avoid SELECT *:

-- Bad: Fetches unnecessary columns
SELECT * FROM users WHERE id = 123;

-- Good: Fetch only what you need
SELECT id, email, name FROM users WHERE id = 123;

Use WHERE Clause Efficiently:

-- Bad: Function prevents index usage
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Good: Create functional index or use exact match
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Then:
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Or store normalized data
SELECT * FROM users WHERE email = '[email protected]';

Optimize JOINs:

-- Bad: Cartesian product then filter
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at > '2024-01-01';

-- Good: Filter before join
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- Better: Filter both tables
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;

Detailed patterns and worked examples

Detailed pattern documentation lives in references/details.md. Read that file when the navigation tier above is insufficient.

Best Practices

Index Selectively: Too many indexes slow down writes
Monitor Query Performance: Use slow query logs
Keep Statistics Updated: Run ANALYZE regularly
Use Appropriate Data Types: Smaller types = better performance
Normalize Thoughtfully: Balance normalization vs performance
Cache Frequently Accessed Data: Use application-level caching
Connection Pooling: Reuse database connections
Regular Maintenance: VACUUM, ANALYZE, rebuild indexes

-- Update statistics
ANALYZE users;
ANALYZE VERBOSE orders;

-- Vacuum (PostgreSQL)
VACUUM ANALYZE users;
VACUUM FULL users;  -- Reclaim space (locks table)

-- Reindex
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

Common Pitfalls

Over-Indexing: Each index slows down INSERT/UPDATE/DELETE
Unused Indexes: Waste space and slow writes
Missing Indexes: Slow queries, full table scans
Implicit Type Conversion: Prevents index usage
OR Conditions: Can't use indexes efficiently
LIKE with Leading Wildcard: LIKE '%abc' can't use index
Function in WHERE: Prevents index usage unless functional index exists

Monitoring Queries

-- Find slow queries (PostgreSQL)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Find missing indexes (PostgreSQL)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Find unused indexes (PostgreSQL)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Files

ATTRIBUTION.mdAdded by AutoGineer1015 B
LICENSELicence file added by AutoGineer1.0 KiB
SKILL.md5.8 KiB
references/details.md6.8 KiB

SKILL.md

SQL Optimization Patterns

Transform slow database queries into lightning-fast operations through systematic optimization, proper indexing, and query plan analysis.

When to Use This Skill

Debugging slow-running queries
Designing performant database schemas
Optimizing application response times
Reducing database load and costs
Improving scalability for growing datasets
Analyzing EXPLAIN query plans
Implementing efficient indexes
Resolving N+1 query problems

Core Concepts

1. Query Execution Plans (EXPLAIN)

Understanding EXPLAIN output is fundamental to optimization.

PostgreSQL EXPLAIN:

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- With actual execution stats
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

-- Verbose output with more details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';

Key Metrics to Watch:

Seq Scan: Full table scan (usually slow for large tables)
Index Scan: Using index (good)
Index Only Scan: Using index without touching table (best)
Nested Loop: Join method (okay for small datasets)
Hash Join: Join method (good for larger datasets)
Merge Join: Join method (good for sorted data)
Cost: Estimated query cost (lower is better)
Rows: Estimated rows returned
Actual Time: Real execution time

2. Index Strategies

Indexes are the most powerful optimization tool.

Index Types:

B-Tree: Default, good for equality and range queries
Hash: Only for equality (=) comparisons
GIN: Full-text search, array queries, JSONB
GiST: Geometric data, full-text search
BRIN: Block Range INdex for very large tables with correlation

-- Standard B-Tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index (index subset of rows)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Covering index (include additional columns)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);

-- Full-text search index
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || body));

-- JSONB index
CREATE INDEX idx_metadata ON events USING GIN(metadata);

3. Query Optimization Patterns

Avoid SELECT *:

-- Bad: Fetches unnecessary columns
SELECT * FROM users WHERE id = 123;

-- Good: Fetch only what you need
SELECT id, email, name FROM users WHERE id = 123;

Use WHERE Clause Efficiently:

-- Bad: Function prevents index usage
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Good: Create functional index or use exact match
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Then:
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Or store normalized data
SELECT * FROM users WHERE email = '[email protected]';

Optimize JOINs:

-- Bad: Cartesian product then filter
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at > '2024-01-01';

-- Good: Filter before join
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- Better: Filter both tables
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;

Detailed patterns and worked examples

Detailed pattern documentation lives in references/details.md. Read that file when the navigation tier above is insufficient.

Best Practices

Index Selectively: Too many indexes slow down writes
Monitor Query Performance: Use slow query logs
Keep Statistics Updated: Run ANALYZE regularly
Use Appropriate Data Types: Smaller types = better performance
Normalize Thoughtfully: Balance normalization vs performance
Cache Frequently Accessed Data: Use application-level caching
Connection Pooling: Reuse database connections
Regular Maintenance: VACUUM, ANALYZE, rebuild indexes

-- Update statistics
ANALYZE users;
ANALYZE VERBOSE orders;

-- Vacuum (PostgreSQL)
VACUUM ANALYZE users;
VACUUM FULL users;  -- Reclaim space (locks table)

-- Reindex
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

Common Pitfalls

Over-Indexing: Each index slows down INSERT/UPDATE/DELETE
Unused Indexes: Waste space and slow writes
Missing Indexes: Slow queries, full table scans
Implicit Type Conversion: Prevents index usage
OR Conditions: Can't use indexes efficiently
LIKE with Leading Wildcard: LIKE '%abc' can't use index
Function in WHERE: Prevents index usage unless functional index exists

Monitoring Queries

-- Find slow queries (PostgreSQL)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Find missing indexes (PostgreSQL)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Find unused indexes (PostgreSQL)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Requirements and compatibility

Works with: Claude Code, Claude apps, OpenAI Codex, GitHub Copilot, Cursor, Gemini CLI, Kiro, OpenCode, Goose, Amp, Roo Code, JetBrains Junie, Factory Droid, Devin (ex-Windsurf), Mistral Vibe, OpenHands, Letta

“Works with” means the tool reads the open Agent Skills format; a skill written for one tool may use features others lack.

Security

No issues found by our static scan· No issues found

A static scan is not a guarantee. Skills are third-party content: review a skill before you let an agent use it.

Attribution and licence

Created by

Seth Hobson

Publisher

Seth Hobson (Community)

Source

wshobson/agents · plugins/developer-essentials/skills/sql-optimization-patterns · commit 4bde316

Licence

MIT License (redistribution allowed)

LICENSE (added by AutoGineer from LICENSE)
ATTRIBUTION.md (written by AutoGineer)

Synced

2026-11-08

AutoGineer is not affiliated with or endorsed by Seth Hobson.

Download ZIP

The ZIP holds the skill’s files, its licence files and an ATTRIBUTION.md. Download sql-optimization-patterns.zip

From the author

AutoGineer summary

What it does

When to use it

Advantages

Usage examples

Install

Documentation by Seth Hobson

SQL Optimization Patterns

When to Use This Skill

Core Concepts

1. Query Execution Plans (EXPLAIN)

2. Index Strategies

3. Query Optimization Patterns

Detailed patterns and worked examples

Best Practices

Common Pitfalls

Monitoring Queries

Files

SKILL.md

SQL Optimization Patterns

When to Use This Skill

Core Concepts

1. Query Execution Plans (EXPLAIN)

2. Index Strategies

3. Query Optimization Patterns

Detailed patterns and worked examples

Best Practices

Common Pitfalls

Monitoring Queries

Requirements and compatibility

Security

Attribution and licence

Related skills

Implement Spec

MCP Builder

Schema

Postgresql Table Design

API Scaffolding Fastapi Pro

Saga Orchestration

Download ZIP