Sql Optimization Patterns

by wshobson46891e7e60daNo licenseListed Oct 8, 2026Updated Oct 8, 2026

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.

Instructions onlyData & Analytics Software Development

AI-generated overview

Guides SQL query optimization, indexing strategies, and EXPLAIN plan analysis to speed up slow database queries.

What it does: Provides reference guidance on reading EXPLAIN and EXPLAIN ANALYZE output, choosing index types such as B-Tree, GIN, and BRIN, and rewriting queries to avoid patterns like SELECT * and functions in WHERE clauses. It also covers join optimization, statistics maintenance, and monitoring queries for slow, missing, or unused indexes. Detailed patterns and worked examples live in a separate reference file.
When to use it: Use when debugging slow-running queries, designing performant database schemas, or reducing database load and improving scalability. It also fits analyzing query plans, implementing indexes, and resolving N+1 query problems.
Requirements: Instructions only; no scripts are shipped. Examples target PostgreSQL features such as EXPLAIN, pg_stat_statements, and VACUUM, so access to a database and appropriate privileges help when applying the guidance.

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 = '[email protected]';
-- With actual execution statsEXPLAIN ANALYZESELECT * FROM users WHERE email = '[email protected]';
-- 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 = '[email protected]';
-- With actual execution statsEXPLAIN ANALYZESELECT * FROM users WHERE email = '[email protected]';
-- 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) = '[email protected]';
-- Good: Create functional index or use exact matchCREATE INDEX idx_users_email_lower ON users(LOWER(email));-- Then:SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Or store normalized dataSELECT * FROM users WHERE email = '[email protected]';

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

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;

Source and attribution

Source:wshobson/agentsinplugins/developer-essentials/skills/sql-optimization-patternsat commit46891e7

License: No license

Content belongs to its original authors. SourceWeft indexes it from a public repository.

Report or request removal

More from wshobson/agents

Uv Package Manager

wshobson

Reference guide for using the uv Python package manager for dependencies, virtual environments and project workflows.

Software DevelopmentOct 8, 2026

Web Component Design

wshobson

Master React, Vue, and Svelte component patterns including CSS-in-JS, composition strategies, and reusable component architecture. Use when building UI component libraries, designing component APIs, or implementing frontend design systems.

Awaiting classificationOct 8, 2026

Python Type Safety

wshobson

Guides Python type annotations, generics, protocols, and strict mypy/pyright checking.

Software DevelopmentOct 8, 2026

Visual Design Foundations

wshobson

Guides typography, color, spacing and iconography decisions for cohesive, accessible visual design systems.

Design & CreativeOct 8, 2026

Responsive Design

wshobson

Guides implementation of responsive web layouts using container queries, fluid typography, CSS Grid and breakpoints.

Software DevelopmentOct 8, 2026

Python Resource Management

wshobson

Python resource management with context managers, cleanup patterns, and streaming. Use when managing connections, file handles, implementing cleanup logic, or building streaming responses with accumulated state.

Awaiting classificationOct 8, 2026

Sql Optimization Patterns

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

Source and attribution

More from wshobson/agents

Uv Package Manager

Web Component Design

Python Type Safety

Visual Design Foundations

Responsive Design

Python Resource Management

More in Data & Analytics

Spanner Basics

Azure Resource Visualizer

Gke Cost Analysis

Azure Kusto

Kql

Datalineage Bigquery Asset Impact Analysis