skills.rest
MrJmpl3
MrJmpl3COMMUNITY
Explore all skills by this author →
𝕏@MrJmpl3GitHub

0172-sql-optimization-patterns

Improves and analyzes slow-running SQL queries with reusable patterns.

Updated Aug 23, 2026
Search Tags:#query optimization#indexing#postgresql#aggregation#sql#pagination#explain analyze
One-click install
npx skills add https://github.com/MrJmpl3/codex_____data_____configuration --skill 0172-sql-optimization-patterns
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: 0172-sql-optimization-patterns
Source: https://github.com/MrJmpl3/codex_____data_____configuration/tree/main/skills/0172-sql-optimization-patterns
Command: npx skills add https://github.com/MrJmpl3/codex_____data_____configuration --skill 0172-sql-optimization-patterns

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

This Skill helps you dramatically improve slow database performance by diagnosing query execution behavior and applying proven indexing and query-shaping patterns.

Core Features & Use Cases

  • Query Plan Diagnosis with EXPLAIN: Learn how to interpret Seq Scan vs Index/Index Only Scan, join strategies, and the difference between estimated costs and actual execution times (EXPLAIN vs EXPLAIN ANALYZE).
  • Indexing Strategies That Match Your Queries: Choose B-Tree, composite, partial, expression, covering (INCLUDE), GIN, GiST, and BRIN indexes to eliminate full scans and support efficient filtering, joins, and search.
  • Optimization Patterns for Common Bottlenecks: Fix N+1 query problems, replace inefficient OFFSET pagination with cursor-based pagination, speed up COUNT/GROUP BY, and rewrite correlated subqueries and aggregations.

Use case example: When a slow endpoint shows long response times, you analyze the failing query with EXPLAIN ANALYZE, identify missing or mismatched indexes, then apply a pattern (e.g., cursor pagination or a JOIN-based aggregation) to reduce execution time and database load.

Quick Start

Ask the AI to review your slow SQL statement and the relevant EXPLAIN ANALYZE output, then recommend specific index definitions and query rewrites using the patterns from this Skill.

Frequently Asked Questions about 0172-sql-optimization-patterns

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

FAQPage Schema
How do I diagnose slow PostgreSQL queries using EXPLAIN ANALYZE?▼

Diagnose slow PostgreSQL queries by running EXPLAIN ANALYZE to compare estimated costs against actual execution times, revealing Seq Scan versus Index Scan bottlenecks. This Skill interprets those execution plan metrics to identify missing indexes and expensive join strategies.

What is the best way to optimize SQL pagination for large datasets?▼

Optimize SQL pagination for large datasets by replacing inefficient OFFSET clauses with cursor-based pagination. This Skill rewrites query patterns to use cursor pagination, significantly reducing scan costs and execution time when navigating deep result sets.

How do I eliminate N+1 query problems in SQL?▼

Eliminate N+1 query problems by rewriting application queries to use JOIN-based batch loading instead of looping individual lookups. This Skill applies batch loading transformations to consolidate queries, lowering database load and reducing overall execution time.

When should I use covering indexes versus partial indexes in PostgreSQL?▼

Use covering indexes with INCLUDE clauses when queries select columns not in the filter, and partial indexes when filtering a specific subset of rows. This Skill selects appropriate index types to eliminate full scans and support efficient filtering.

How do I speed up COUNT and GROUP BY aggregation in PostgreSQL?▼

Speed up COUNT and GROUP BY aggregation by rewriting correlated subqueries and applying targeted indexing strategies. This Skill transforms inefficient aggregation patterns to reduce scan costs and improve query performance on large datasets.

Related Skills

Explore More Data & Analytics Skills→
AratKruglikAratKruglik
150

database-optimizer

Analyze slow SQL queries and execution plans for PostgreSQL and MySQL.

Community
Advanced
NotysotyNotysoty
9

SQL Query Optimizer

Analyze SQL queries for performance issues and rewrite them with optimized execution plans.

Community
Advanced
thehivegroup-aithehivegroup-ai

postgres-standards

Manage PostgreSQL schema design, migrations, and query optimization with psycopg2.

Official
Intermediate
cathayriskcathayrisk

supabase-postgres-best-practices

Analyze SQL and EXPLAIN plans to optimize Supabase Postgres queries and schemas.

Community
Advanced