Datawshobson-agents

sql-optimization-patterns

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.

Open in Skill Hub View pinned source

3.9k est. installs39.1k stars

Estimated installs are a discovery signal based on public repository popularity and source completeness, not recorded Dokki installation events.

Repository

wshobson/agents

Pinned commit

d82998e7df39

License

MIT

Execution

Instructions only

Complete source instructions

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:

sql

-- Basic explainEXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
-- With actual execution statsEXPLAIN ANALYZESELECT * FROM users WHERE email = 'user@example.com';
-- Verbose output with more detailsEXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT u.*, o.order_totalFROM users uJOIN orders o ON u.id = o.user_idWHERE u.created_at > NOW() - INTERVAL '30 days';

-- Basic explainEXPLAIN SELECT * FROM users WHERE email = 'user@example.com';
-- With actual execution statsEXPLAIN ANALYZESELECT * FROM users WHERE email = 'user@example.com';
-- Verbose output with more detailsEXPLAIN (ANALYZE, BUFFERS, VERBOSE)SELECT u.*, o.order_totalFROM users uJOIN orders o ON u.id = o.user_idWHERE 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

sql

-- Standard B-Tree indexCREATE 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 indexCREATE 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 indexCREATE INDEX idx_posts_search ON postsUSING GIN(to_tsvector('english', title || ' ' || body));
-- JSONB indexCREATE INDEX idx_metadata ON events USING GIN(metadata);

-- Standard B-Tree indexCREATE 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 indexCREATE 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 indexCREATE INDEX idx_posts_search ON postsUSING GIN(to_tsvector('english', title || ' ' || body));
-- JSONB indexCREATE INDEX idx_metadata ON events USING GIN(metadata);

3. Query Optimization Patterns

Avoid SELECT *:

sql

-- Bad: Fetches unnecessary columnsSELECT * FROM users WHERE id = 123;
-- Good: Fetch only what you needSELECT id, email, name FROM users WHERE id = 123;

-- Bad: Fetches unnecessary columnsSELECT * FROM users WHERE id = 123;
-- Good: Fetch only what you needSELECT id, email, name FROM users WHERE id = 123;

Use WHERE Clause Efficiently:

sql

-- Bad: Function prevents index usageSELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Good: Create functional index or use exact matchCREATE INDEX idx_users_email_lower ON users(LOWER(email));-- Then:SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Or store normalized dataSELECT * FROM users WHERE email = 'user@example.com';

-- Bad: Function prevents index usageSELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Good: Create functional index or use exact matchCREATE INDEX idx_users_email_lower ON users(LOWER(email));-- Then:SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- Or store normalized dataSELECT * FROM users WHERE email = 'user@example.com';

Optimize JOINs:

sql

-- Bad: Cartesian product then filterSELECT u.name, o.totalFROM users u, orders oWHERE u.id = o.user_id AND u.created_at > '2024-01-01';
-- Good: Filter before joinSELECT u.name, o.totalFROM users uJOIN orders o ON u.id = o.user_idWHERE u.created_at > '2024-01-01';
-- Better: Filter both tablesSELECT u.name, o.totalFROM (SELECT * FROM users WHERE created_at > '2024-01-01') uJOIN orders o ON u.id = o.user_id;

-- Bad: Cartesian product then filterSELECT u.name, o.totalFROM users u, orders oWHERE u.id = o.user_id AND u.created_at > '2024-01-01';
-- Good: Filter before joinSELECT u.name, o.totalFROM users uJOIN orders o ON u.id = o.user_idWHERE u.created_at > '2024-01-01';
-- Better: Filter both tablesSELECT u.name, o.totalFROM (SELECT * FROM users WHERE created_at > '2024-01-01') uJOIN 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

sql

-- Update statisticsANALYZE users;ANALYZE VERBOSE orders;
-- Vacuum (PostgreSQL)VACUUM ANALYZE users;VACUUM FULL users;  -- Reclaim space (locks table)
-- ReindexREINDEX INDEX idx_users_email;REINDEX TABLE users;

-- Update statisticsANALYZE users;ANALYZE VERBOSE orders;
-- Vacuum (PostgreSQL)VACUUM ANALYZE users;VACUUM FULL users;  -- Reclaim space (locks table)
-- ReindexREINDEX 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

sql

-- Find slow queries (PostgreSQL)SELECT query, calls, total_time, mean_timeFROM pg_stat_statementsORDER BY mean_time DESCLIMIT 10;
-- Find missing indexes (PostgreSQL)SELECT    schemaname,    tablename,    seq_scan,    seq_tup_read,    idx_scan,    seq_tup_read / seq_scan AS avg_seq_tup_readFROM pg_stat_user_tablesWHERE seq_scan > 0ORDER BY seq_tup_read DESCLIMIT 10;
-- Find unused indexes (PostgreSQL)SELECT    schemaname,    tablename,    indexname,    idx_scan,    idx_tup_read,    idx_tup_fetchFROM pg_stat_user_indexesWHERE idx_scan = 0ORDER BY pg_relation_size(indexrelid) DESC;

-- Find slow queries (PostgreSQL)SELECT query, calls, total_time, mean_timeFROM pg_stat_statementsORDER BY mean_time DESCLIMIT 10;
-- Find missing indexes (PostgreSQL)SELECT    schemaname,    tablename,    seq_scan,    seq_tup_read,    idx_scan,    seq_tup_read / seq_scan AS avg_seq_tup_readFROM pg_stat_user_tablesWHERE seq_scan > 0ORDER BY seq_tup_read DESCLIMIT 10;
-- Find unused indexes (PostgreSQL)SELECT    schemaname,    tablename,    indexname,    idx_scan,    idx_tup_read,    idx_tup_fetchFROM pg_stat_user_indexesWHERE idx_scan = 0ORDER BY pg_relation_size(indexrelid) DESC;

Same repository

sql-optimization-patterns

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

api-design-principles

architecture-patterns

avoid-ai-writing

before-you-build

block-no-verify-hook

brand-landingpage

Start your company of agents
today.

sql-optimization-patterns

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

Related Skills

api-design-principles

architecture-patterns

avoid-ai-writing

before-you-build

block-no-verify-hook

brand-landingpage

Start your company of agentstoday.

Start your company of agents
today.