skills.rest
archibate
archibateCOMMUNITY
Explore all skills by this author →
Website𝕏@archibateGitHub

sql-optimization-patterns

Analyze EXPLAIN plans and recommend indexes for PostgreSQL and MySQL queries.

6|Updated Feb 25, 2026
Search Tags:#performance#indexing#database#postgresql#sql#query-optimization#explain-plan
One-click install
npx skills add https://github.com/archibate/archibate-skills --skill sql-optimization-patterns-archibate
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sql-optimization-patterns
Source: https://github.com/archibate/archibate-skills/tree/main/old-skills/redundant-skills/sql-optimization-patterns
Command: npx skills add https://github.com/archibate/archibate-skills --skill sql-optimization-patterns-archibate

SYSTEM DOCUMENTATION & REQUIREMENTS

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

What problem does it solve?

Systematically diagnose and fix slow SQL queries, inefficient joins, and poor indexing that cause high latency, heavy I/O, and increased database costs.

Core Features & Use Cases

  • EXPLAIN plan analysis: Interpret execution plans to find sequential scans, inefficient joins, and misused indexes.
  • Index strategy: Recommend B-Tree, GIN, GiST, BRIN, functional, partial, and covering indexes and when to use them.
  • Query patterns & refactors: Provide patterns to avoid N+1 queries, optimize pagination, transform correlated subqueries, and batch operations.
  • Operational actions: Guidance on ANALYZE, VACUUM, materialized views, partitioning, and maintenance tasks to sustain performance.
  • Use Case: Debug a slow customer-facing endpoint by analyzing EXPLAIN ANALYZE output, proposing index and query changes, and estimating improvements.

Quick Start

Analyze my slow query with EXPLAIN ANALYZE and recommend index, query, or schema changes along with estimated impact.

Frequently Asked Questions about sql-optimization-patterns

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

FAQPage Schema
How do I optimize slow SQL queries using EXPLAIN plans?▼

Analyze EXPLAIN plans to identify sequential scans and inefficient joins, then apply index strategies and query refactoring to reduce query latency and database I/O load.

What is the best index strategy for PostgreSQL pagination?▼

Use B-Tree and covering indexes combined with keyset pagination query refactoring to eliminate heavy offset operations and avoid full sequential scans in PostgreSQL.

How do I fix N+1 query problems and correlated subqueries in MySQL?▼

Transform correlated subqueries into efficient joins and apply batch operation patterns to eliminate N+1 query problems, reducing execution time and database round trips.

Does SQL optimization work with both PostgreSQL and MySQL for schema design?▼

Yes, SQL optimization supports relational databases like PostgreSQL and MySQL by analyzing execution plans and suggesting schema-level changes including partitioning and materialized views.

When should I use materialized views or partitioning over standard indexes?▼

Use materialized views and table partitioning when standard B-Tree indexing fails to resolve heavy I/O from aggregations, sustaining performance through operational maintenance tasks like VACUUM and ANALYZE.

Related Skills

Explore More Software Engineering Skills→
aliyunaliyun
17

hologres-query-optimizer

Analyze Hologres EXPLAIN outputs and recommend query optimizations.

Official
Advanced
duwscanduwscan

postgres-pro

Analyze PostgreSQL query execution plans and design indexes.

Community
Advanced
4lerman4lerman

performance-profiler

Profile Node.js, Python, and Go applications for CPU, memory, and I/O bottlenecks.

Community
Advanced
AmidVoshakulAmidVoshakul

database-optimizer

Analyze execution plans and implement indexing strategies to optimize database performance.

Community
Advanced