Does it support both PostgreSQL and MySQL?

Yes, the skill includes specific optimization patterns and syntax examples for both PostgreSQL and MySQL, covering the most common performance tuning needs for both engines.

Does it cover advanced database features like partitioning?

Yes, it includes advanced techniques for managing large datasets, such as table partitioning, materialized views, and specialized index types like GIN and GiST.

How does this skill help with slow SQL queries?

It provides systematic patterns for analyzing EXPLAIN plans to identify performance bottlenecks and recommends specific indexing or query restructuring techniques to fix them.

Can this skill help resolve N+1 query problems?

Absolutely. It provides clear architectural patterns for transforming N+1 query anti-patterns into efficient JOINs or batch loading strategies.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: HermeticOrmus

byHermeticOrmus

データベース管理

Optimizes SQL queries and database performance through advanced indexing, EXPLAIN plan analysis, and efficient query patterns.

概要

SQL Optimization Patterns is a comprehensive resource for developers looking to master database performance and eliminate latency. It provides deep insights into query execution plans, teaching users how to interpret EXPLAIN output to identify bottlenecks in PostgreSQL and MySQL. The skill covers advanced indexing strategies—including B-Tree, GIN, and covering indexes—as well as implementation patterns for resolving common issues like N+1 queries and slow offset-based pagination. Whether you're debugging production issues or designing a scalable schema from scratch, this skill offers the systematic guidance needed to build high-performance data layers.

主な機能

Advanced indexing strategies including composite, partial, and functional indexes
Proven patterns for eliminating N+1 query anti-patterns
High-performance pagination techniques using cursor-based methods
Batch processing patterns for efficient large-scale data modifications
0 GitHub stars
In-depth EXPLAIN and EXPLAIN ANALYZE query plan interpretation

ユースケース

Diagnosing and resolving slow-running production database queries
Designing performant database schemas for high-traffic applications
Refactoring legacy application code to reduce database load and infrastructure costs

概要

主な機能

Advanced indexing strategies including composite, partial, and functional indexes
Proven patterns for eliminating N+1 query anti-patterns
High-performance pagination techniques using cursor-based methods
Batch processing patterns for efficient large-scale data modifications
0 GitHub stars
In-depth EXPLAIN and EXPLAIN ANALYZE query plan interpretation

ユースケース

Diagnosing and resolving slow-running production database queries
Designing performant database schemas for high-traffic applications
Refactoring legacy application code to reduce database load and infrastructure costs