What is the benefit of using EXPLAIN ANALYZE with this skill?

The skill helps you interpret the detailed metrics from EXPLAIN ANALYZE, such as actual execution time and buffer usage, allowing you to verify that your optimizations are actually working.

Is this useful for database schema design?

Absolutely. It offers best practices for choosing efficient data types and planning index strategies during the design phase to ensure the database scales effectively as data grows.

Does it support specific databases like PostgreSQL or MySQL?

While many patterns are universal, the skill includes specialized advice for PostgreSQL (like GIN/GiST indexes and partitioning) and MySQL-specific optimization hints.

How does this skill help with slow database queries?

It provides step-by-step guidance on analyzing query execution plans to identify bottlenecks like full table scans and recommends specific indexing or query refactoring strategies to improve execution speed.

Can this skill help resolve N+1 query problems?

Yes, it provides specific patterns for identifying N+1 issues in application logic and offers SQL-based solutions like batch loading or optimized JOINs to reduce database roundtrips.

SQL Optimization Patterns

Name: SQL Optimization Patterns
Author: Microck

byMicrock

•

データベース管理

Optimizes SQL database performance through advanced indexing strategies, execution plan analysis, and efficient query refactoring.

概要

This skill empowers developers to transform slow database operations into high-performance queries by applying systematic optimization techniques. It provides expert guidance on interpreting execution plans (EXPLAIN), implementing sophisticated indexing strategies such as composite, partial, and GIN indexes, and resolving common performance bottlenecks like N+1 queries and inefficient pagination. Whether you are debugging a production bottleneck or designing a scalable schema from scratch, this skill offers the patterns and best practices needed to reduce database load, minimize latency, and lower infrastructure costs across various RDBMS platforms including PostgreSQL and MySQL.

主な機能

Techniques for high-performance pagination and large-scale batch data processing
Patterns for eliminating N+1 query bottlenecks and optimizing complex JOIN operations
Implementation of advanced indexing strategies including covering, partial, and expression indexes
Guidance on advanced database features like partitioning, materialized views, and vacuuming
In-depth analysis and interpretation of EXPLAIN and EXPLAIN ANALYZE query plans
81 GitHub stars

ユースケース

Reducing cloud database costs by optimizing resource consumption and query efficiency
Designing performant database schemas for data-intensive applications
Refactoring legacy application queries that are causing performance timeouts

概要

主な機能

Techniques for high-performance pagination and large-scale batch data processing
Patterns for eliminating N+1 query bottlenecks and optimizing complex JOIN operations
Implementation of advanced indexing strategies including covering, partial, and expression indexes
Guidance on advanced database features like partitioning, materialized views, and vacuuming
In-depth analysis and interpretation of EXPLAIN and EXPLAIN ANALYZE query plans
81 GitHub stars

ユースケース

Reducing cloud database costs by optimizing resource consumption and query efficiency
Designing performant database schemas for data-intensive applications
Refactoring legacy application queries that are causing performance timeouts