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