Agent skill · data analytics · aiskillstore
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.
Why this skill is useful
Provides specific indexing strategies and query optimization patterns that the AI wouldn't reliably generate on its own.
What it needs
About 7k tokens when loaded. Last updated 2026-08-07. 405 stars on the source repository.
What this skill does
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: Optimization Patterns Pattern 1: Eliminate N+1 Queries Problem: N+1 Query Anti-Pattern Solution: Use JOINs or Batch Loading Pattern 2: Optimize Pagination Bad: OFFSET on Large Tables Good: Cursor-Based Pagination Pattern 3: Aggregate Efficiently Optimize COUNT Queries: Optimize GROUP BY: Pattern 4: Subquery Optimization Transform Correlated Subqueries: Use CTEs for Clarity: Pattern 5: Batch Operations Batch INSERT: Batch UPDATE: Advanced Techniques Materialized Views Pre-compute expensive queries. Partitioning Split large tables for better performance. …
How to use it
Reference it in AdaL, Claude Code, Cursor or any coding agent — nothing to install:
@skills aiskillstore/sql-optimization-patterns--954ac2