skills.rest
Dianshu-Liao
Dianshu-LiaoCOMMUNITY
Explore all skills by this author →
GitHub

sql-optimization-patterns

Analyze EXPLAIN output and rewrite SQL with indexing patterns.

2|Updated May 20, 2026
Search Tags:#query optimization#indexing#sql#pagination#database performance#explain analyze#query planning
One-click install
npx skills add https://github.com/Dianshu-Liao/SkilLGuard --skill sql-optimization-patterns-dianshu-liao
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sql-optimization-patterns
Source: https://github.com/Dianshu-Liao/SkilLGuard/tree/main/data/skills/sql-optimization-patterns
Command: npx skills add https://github.com/Dianshu-Liao/SkilLGuard --skill sql-optimization-patterns-dianshu-liao

SYSTEM DOCUMENTATION & REQUIREMENTS

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

What problem does it solve?

This Skill helps you diagnose and eliminate slow database queries by turning inefficient SQL patterns into measurable performance improvements.

Core Features & Use Cases

  • EXPLAIN-driven analysis: Interpret query plan output (including EXPLAIN ANALYZE) to pinpoint bottlenecks like sequential scans, join inefficiencies, and costly row estimates.
  • Index strategy design: Choose appropriate index types (B-Tree, GIN, GiST, BRIN, functional/expression, composite, partial, covering) based on query predicates and access patterns.
  • Practical optimization patterns: Apply common fixes for N+1 queries, pagination inefficiencies, heavy aggregations, correlated subqueries, and batch operation improvements.
  • Advanced performance techniques: Use materialized views, partitioning strategies, and maintenance guidance (VACUUM/ANALYZE/REINDEX) to sustain gains at scale.
  • Built-in resources checklist: Leverage included scripts and guides to generate actionable next steps for indexing and slow-query investigation.

Quick Start

Use the skill to optimize a slow query by asking: "Analyze this SQL and its EXPLAIN (or EXPLAIN ANALYZE) output, identify the biggest performance issues, then recommend specific indexes and rewrite the query using safer optimization patterns."

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 ANALYZE output?▼

To optimize slow SQL queries, analyze EXPLAIN ANALYZE output to pinpoint bottlenecks like sequential scans and costly row estimates. This Skill interprets query execution metrics to recommend specific indexes and rewrite inefficient SQL patterns for measurable performance improvements.

What is the best way to fix N+1 query problems and improve pagination performance?▼

The best way to fix N+1 query problems and improve pagination is by applying SQL rewrite patterns. This Skill identifies inefficient pagination and correlated subqueries, recommending concrete rewrites and appropriate composite indexes to eliminate unnecessary repetitive database access.

How do I choose the right index type for my database queries?▼

Choosing the right index type requires analyzing query predicates and access patterns. This Skill helps you select between B-Tree, GIN, GiST, BRIN, functional, partial, and covering indexes based on your specific SQL workload to maximize query performance.

Does this query optimization approach work with both PostgreSQL and MySQL workloads?▼

Yes, this query optimization approach applies to both PostgreSQL and MySQL-style workloads. It reasons over EXPLAIN and EXPLAIN ANALYZE metrics to troubleshoot slow queries, design performant schemas, and apply optimization patterns across these database systems.

What should I do when my database queries are still slow after adding indexes?▼

When queries remain slow after adding indexes, you need advanced techniques like materialized views, partitioning, and maintenance operations. This Skill provides guidance on VACUUM, ANALYZE, and REINDEX to sustain performance gains and resolve deep-rooted query planning issues.

How do I troubleshoot heavy aggregations and sequential scans in my query execution plans?▼

To troubleshoot heavy aggregations and sequential scans, analyze your query execution plan output to identify costly row estimates and join inefficiencies. This Skill interprets these metrics to recommend specific index strategies and SQL rewrites that eliminate the bottlenecks.

Related Skills

Explore More Data & Analytics Skills→
archibatearchibate
6

sql-optimization-patterns

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

Community
Advanced
patricio0312revpatricio0312rev
5

sql-query-optimizer

Analyze SQL EXPLAIN plans and propose indexes and rewrites to optimize slow queries.

Community
Advanced
DVNghiemDVNghiem
20

postgres-patterns

Optimize PostgreSQL schema, indexing and query performance with proven patterns.

Community
Intermediate
monch1962monch1962
3

clickhouse-io

Design ClickHouse schemas and optimize analytical queries for large datasets.

Community
Basic