Sql Optimization Patterns

作者 wshobson46891e7e60da无许可证收录于 2026年10月8日更新于 2026年10月8日

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.

仅含说明Data & Analytics Software Development

AI 生成的概览

指导 SQL 查询优化、索引策略与 EXPLAIN 执行计划分析，以加快缓慢的数据库查询。

功能: 提供参考指导，讲解如何解读 EXPLAIN 与 EXPLAIN ANALYZE 输出、选择 B-Tree、GIN、BRIN 等索引类型，以及重写查询以避开 SELECT *、在 WHERE 中使用函数等模式。内容还涵盖连接优化、统计信息维护，以及监控慢查询、缺失索引和未使用索引。详细的模式与示例放在单独的参考文件中。
适用场景: 适用于调试运行缓慢的查询、设计高性能数据库架构，或降低数据库负载并提升可扩展性。也适合分析查询计划、实施索引以及解决 N+1 查询问题。
运行要求: 仅为说明性内容，不附带脚本。示例针对 PostgreSQL 的 EXPLAIN、pg_stat_statements、VACUUM 等功能，因此实际应用时需要数据库访问权限和相应权限。

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;

来源与署名

来源：wshobson/agents位于plugins/developer-essentials/skills/sql-optimization-patterns提交46891e7

许可证：无许可证

内容归原作者所有。SourceWeft 从公开仓库中收录这些内容。

举报或申请下架

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

来源与署名

更多来自 wshobson/agents 的技能

Uv Package Manager

Web Component Design

Python Type Safety

Visual Design Foundations

Responsive Design

Python Resource Management

更多Data & Analytics技能

Content Strategy

Cash Flow Snapshot

Pipeline Review

Metrics Review

Performance Report

People Report