0172-sql-optimization-patterns

What problem does it solve?

This Skill helps you dramatically improve slow database performance by diagnosing query execution behavior and applying proven indexing and query-shaping patterns.

Core Features & Use Cases

• Query Plan Diagnosis with EXPLAIN: Learn how to interpret Seq Scan vs Index/Index Only Scan, join strategies, and the difference between estimated costs and actual execution times (EXPLAIN vs EXPLAIN ANALYZE).
• Indexing Strategies That Match Your Queries: Choose B-Tree, composite, partial, expression, covering (INCLUDE), GIN, GiST, and BRIN indexes to eliminate full scans and support efficient filtering, joins, and search.
• Optimization Patterns for Common Bottlenecks: Fix N+1 query problems, replace inefficient OFFSET pagination with cursor-based pagination, speed up COUNT/GROUP BY, and rewrite correlated subqueries and aggregations.

Use case example: When a slow endpoint shows long response times, you analyze the failing query with EXPLAIN ANALYZE, identify missing or mismatched indexes, then apply a pattern (e.g., cursor pagination or a JOIN-based aggregation) to reduce execution time and database load.

Quick Start

Ask the AI to review your slow SQL statement and the relevant EXPLAIN ANALYZE output, then recommend specific index definitions and query rewrites using the patterns from this Skill.