skills.rest
mvandermeulen
mvandermeulenCOMMUNITY
Explore all skills by this author →
GitHub

sql-optimization-patterns

Analyze SQL execution plans and suggest indexing strategies for slow queries.

2|1|Updated Sep 16, 2024
Search Tags:#performance#indexing#database#postgresql#optimization#sql
One-click install
npx skills add https://github.com/mvandermeulen/nvim --skill sql-optimization-patterns-mvandermeulen
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sql-optimization-patterns
Source: https://github.com/mvandermeulen/nvim/tree/main/.claude/skills/sql-optimization-patterns
Command: npx skills add https://github.com/mvandermeulen/nvim --skill sql-optimization-patterns-mvandermeulen

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes scripts (resource) and references (resource) and assets (resource) components.

What problem does it solve?

This skill addresses database performance bottlenecks by providing systematic methods to identify, analyze, and resolve slow-running queries, high-latency joins, and inefficient indexing strategies.

Core Features & Use Cases

  • Query Plan Analysis: Master the use of EXPLAIN and EXPLAIN ANALYZE to diagnose performance issues at the execution level.
  • Indexing Strategies: Implement advanced indexing techniques including B-Tree, GIN, and partial indexes to accelerate data retrieval.
  • Performance Patterns: Resolve common anti-patterns like N+1 queries, inefficient pagination, and correlated subqueries to ensure database scalability.

Quick Start

Use the sql-optimization-patterns skill to analyze the provided query execution plan and suggest appropriate indexing strategies for the slow table scan.

Frequently Asked Questions about sql-optimization-patterns

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I use EXPLAIN ANALYZE to debug slow SQL queries?▼

Use EXPLAIN ANALYZE to debug slow SQL queries by generating an execution plan that exposes performance bottlenecks at the operator level. This skill helps interpret table scans and join inefficiencies to pinpoint latency sources.

What is the best indexing strategy for PostgreSQL query optimization?▼

PostgreSQL query optimization strategies include implementing B-Tree, GIN, and partial indexes to accelerate data retrieval. This skill provides patterns to select appropriate index types based on execution plans and schema design.

How do I fix N+1 queries and other database performance anti-patterns?▼

Fix N+1 queries and database performance anti-patterns by applying systematic resolution patterns for inefficient pagination and correlated subqueries. This skill provides methods to ensure database scalability and faster response times.

Do I need to understand database internals to optimize SQL execution plans?▼

Optimizing SQL execution plans requires knowledge of database management system internals, SQL execution plans, and index types. This skill applies these concepts to analyze slow queries and improve application response times.

When should I use partial indexes instead of standard B-Tree indexes?▼

Partial indexes optimize data retrieval when queries target a specific subset of rows, reducing index size and maintenance overhead compared to standard B-Tree indexes. This skill guides selecting the right index based on execution analysis.

Related Skills

Explore More Software Engineering Skills→
AmidVoshakulAmidVoshakul

sql-pro

Analyze SQL execution plans and optimize indexing strategies for databases.

Community
Advanced
icartshicartsh
4

sql-optimization-patterns

Analyze EXPLAIN plans and implement indexing strategies to optimize SQL queries.

Community
Advanced
kevinnguyen271090kevinnguyen271090
15

databases

Design and optimize MongoDB and PostgreSQL databases for schema, queries, and performance.

Community
Intermediate
plurigridplurigrid
60

database-design

Design database schemas and safe migration patterns for PostgreSQL, MySQL, and NoSQL.

Official
Intermediate