How does this help with N+1 query problems?

It provides specific code patterns and SQL structures to convert repetitive application-level loops into efficient batch queries or relational JOINs.

Does this skill support specific database engines?

The core optimization patterns are applicable to most SQL databases, with specific implementation examples provided for PostgreSQL and MySQL.

Can it help with very large datasets?

Yes, it includes advanced architectural techniques like table partitioning and materialized views specifically designed to maintain performance at scale.

Will this skill help me understand EXPLAIN outputs?

Absolutely. It breaks down key metrics like Sequential Scans vs. Index Scans and different Join methods to help you understand exactly how the database executes your query.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: HermeticOrmus

byHermeticOrmus

データベース管理

Optimizes SQL queries and database schemas using advanced indexing, execution plan analysis, and performance patterns.

概要

This skill empowers developers to transform sluggish database operations into high-performance queries by applying systematic optimization techniques. It provides expert guidance on interpreting EXPLAIN plans, implementing strategic indexing (B-Tree, GIN, Composite), and resolving common performance bottlenecks like N+1 queries or inefficient pagination. Whether you are designing a new schema or debugging a production database, this skill offers the patterns and best practices needed to reduce latency, lower costs, and improve application scalability across various SQL environments.

主な機能

Deep analysis and interpretation of SQL EXPLAIN execution plans to identify bottlenecks
Guidance on advanced techniques like materialized views and table partitioning for large datasets
0 GitHub stars
Performance-driven pagination using cursor-based methods to replace slow OFFSET operations
Elimination of N+1 query anti-patterns through optimized JOINs and batch loading
Implementation of advanced indexing strategies including composite, partial, and covering indexes

ユースケース

Designing performant and scalable database schemas for high-traffic applications
Debugging and fixing slow-running production database queries to improve user experience
Reducing cloud database compute costs by optimizing query efficiency and resource usage

概要

主な機能

Deep analysis and interpretation of SQL EXPLAIN execution plans to identify bottlenecks
Guidance on advanced techniques like materialized views and table partitioning for large datasets
0 GitHub stars
Performance-driven pagination using cursor-based methods to replace slow OFFSET operations
Elimination of N+1 query anti-patterns through optimized JOINs and batch loading
Implementation of advanced indexing strategies including composite, partial, and covering indexes

ユースケース

Designing performant and scalable database schemas for high-traffic applications
Debugging and fixing slow-running production database queries to improve user experience
Reducing cloud database compute costs by optimizing query efficiency and resource usage