How does this skill help with slow SQL queries?

It provides systematic patterns for analyzing EXPLAIN plans, identifying bottlenecks like sequential scans, and suggesting specific indexing or query refactoring strategies to improve execution time.

Can it help resolve N+1 query problems in my application code?

Yes, the skill identifies the N+1 anti-pattern and provides specific SQL solutions using JOINs, CTEs, or batch loading to minimize database roundtrips.

Does this work for databases other than PostgreSQL?

Yes. While many examples use PostgreSQL syntax, the core optimization concepts such as indexing strategies, JOIN optimization, and avoiding N+1 queries apply to MySQL, SQL Server, and other relational databases.

What indexing strategies does this skill cover?

It covers B-Tree, GIN, Hash, Partial, Expression, and Covering indexes, explaining exactly when and why to use each type for maximum performance gains.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: HermeticOrmus

byHermeticOrmus

数据库管理

Optimizes SQL query performance through systematic execution plan analysis, advanced indexing strategies, and proven query restructuring patterns.

关于

This skill empowers developers to transform slow database operations into high-performance queries by leveraging systematic optimization techniques. It provides expert guidance on interpreting EXPLAIN plans, implementing advanced indexing strategies (such as B-Tree, GIN, and covering indexes), and resolving common anti-patterns like N+1 queries and inefficient pagination. Whether you are designing a new database schema or debugging performance bottlenecks in a production environment, this skill ensures your data operations remain scalable, responsive, and cost-effective.

主要功能

Advanced indexing strategies including partial, expression, and covering indexes
High-performance pagination techniques using cursor-based keyset filtering
0 GitHub stars
Detection and resolution of N+1 query anti-patterns and Cartesian products
Optimization of complex aggregations, window functions, and subqueries
Expert interpretation of EXPLAIN and EXPLAIN ANALYZE execution plans

使用场景

Accelerating slow-running production database queries to improve application response times
Reducing database CPU load and cloud infrastructure costs through efficient query execution
Designing performant database schemas for applications with large-scale or rapidly growing datasets

关于

主要功能

Advanced indexing strategies including partial, expression, and covering indexes
High-performance pagination techniques using cursor-based keyset filtering
0 GitHub stars
Detection and resolution of N+1 query anti-patterns and Cartesian products
Optimization of complex aggregations, window functions, and subqueries
Expert interpretation of EXPLAIN and EXPLAIN ANALYZE execution plans

使用场景

Accelerating slow-running production database queries to improve application response times
Reducing database CPU load and cloud infrastructure costs through efficient query execution
Designing performant database schemas for applications with large-scale or rapidly growing datasets