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.

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: 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 3. Query Optimization Patterns Avoid SELECT \ : Use WHERE Clause Efficiently: Optimize JOINs: 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 1. Index Selectively : Too many indexes slow down writes 2. Monitor Query Performance : Use slow query logs 3. Keep Statistics Updated : Run ANALYZE regularly 4. Use Appropriate Data Types : Smaller types = better performance 5. Normalize Thoughtfully : Balance normalization vs performance 6. Cache Frequently Accessed Data : Use application level caching 7. Connection Pooling : Reuse database connections 8. Regular Maintenance : VACUUM, ANALYZE, rebuild indexes 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-optimization-patterns