skills.rest
patronus-ai
patronus-aiOFFICIAL
Explore all skills by this author →
GitHub

sql-optimization-patterns

Diagnose PostgreSQL execution plans and apply indexing and query rewrite patterns.

2|2|Updated Mar 26, 2026
Search Tags:#postgresql#pagination#query tuning#sql optimization#database indexing#explain analysis#performance troubleshooting
One-click install
npx skills add https://github.com/patronus-ai/skill-inject --skill sql-optimization-patterns-patronus-ai
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: sql-optimization-patterns
Source: https://github.com/patronus-ai/skill-inject/tree/main/data/skills/sql-optimization-patterns
Command: npx skills add https://github.com/patronus-ai/skill-inject --skill sql-optimization-patterns-patronus-ai

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 fix slow database performance by turning inefficient SQL into optimized queries using indexing strategy and execution-plan analysis.

Core Features & Use Cases

  • EXPLAIN-focused debugging: Interpret PostgreSQL EXPLAIN / EXPLAIN ANALYZE metrics (scan types, join methods, estimated vs actual time).
  • Index optimization: Choose and design the right index type (B-Tree, GIN, GiST, BRIN), including composite, partial, expression, covering, and JSONB indexes.
  • Query rewrite patterns: Apply optimization patterns for N+1 elimination, pagination improvements, COUNT/GROUP BY tuning, and correlated subquery refactoring.

Quick Start

Use the sql-optimization-patterns skill to optimize the query behind your slow endpoint by analyzing its EXPLAIN output and proposing specific index and query rewrites to reduce full scans and unnecessary work.

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 PostgreSQL queries using EXPLAIN analysis?▼

To optimize slow PostgreSQL queries, you analyze the EXPLAIN and EXPLAIN ANALYZE execution plans to identify high-cost full scans, evaluate join methods, and compare estimated versus actual execution times to pinpoint bottlenecks.

What is the best way to fix N+1 queries and slow pagination in SQL?▼

Fixing N+1 queries and slow SQL pagination involves applying targeted query rewrite patterns to eliminate redundant scans and refactoring correlated subqueries for more efficient data aggregation and faster offset-based retrieval.

When do I need to use partial or covering indexes for database indexing?▼

You need partial or covering indexes when standard B-Tree scans remain too slow, allowing you to index specific subsets of data or include additional columns to speed up filtering, sorting, and JSONB lookups without table access.

Can I tune COUNT and GROUP BY workloads in PostgreSQL by rewriting SQL?▼

You can tune COUNT and GROUP BY workloads by refactoring correlated subqueries into efficient aggregation patterns and designing targeted indexes that reduce the row-scanning overhead before the grouping operation occurs.

Does this query tuning approach work for databases other than PostgreSQL?▼

This query tuning approach uses EXPLAIN-driven diagnosis and indexing patterns designed primarily for PostgreSQL, but the underlying SQL rewrite techniques for joins and pagination apply to similar relational database systems.

Related Skills

Explore More Software Engineering Skills→
zmrezmre
1

sql-optimization-patterns

Optimize slow SQL queries using EXPLAIN analysis and indexing strategies.

Community
Basic
Mentor-WizardMentor-Wizard
10

database-optimizer

Analyze slow queries and execution plans for PostgreSQL and MySQL.

Official
Advanced
bcastelinobcastelino
2

database-optimizer

Analyze SQL query execution plans and design indexing strategies.

Community
Advanced
rnavarychrnavarych
14

role-database:query-optimization

Analyze execution plans and recommend index strategies across PostgreSQL, MySQL, MongoDB, and ClickHouse.

Community
Intermediate