sql-optimization-patterns

Diagnose slow SQL queries and apply indexing, EXPLAIN analysis, and query rewriting patterns.

Updated Jul 28, 2026

Search Tags:#query optimization #indexing #postgresql #sql #database performance #explain analyze #n+1 queries

Ready to run

npx skills add https://github.com/truongnat/Restly --skill sql-optimization-patterns-truongnat

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: sql-optimization-patterns
Source: https://github.com/truongnat/Restly/tree/main/.agents/skills/sql-optimization-patterns
Command: npx skills add https://github.com/truongnat/Restly --skill sql-optimization-patterns-truongnat

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Slow database queries, missing indexes, and N+1 query patterns degrade application performance and increase infrastructure costs, and diagnosing them requires deep knowledge of query plans and index design. ## Core Features & Use Cases - EXPLAIN Plan Analysis: Interpret PostgreSQL EXPLAIN ANALYZE output including scan types, join methods, costs, and actual execution times. - Index Strategy Design: Create B-Tree, GIN, partial, covering, composite, and expression indexes matched to query patterns. - Query Rewriting Patterns: Eliminate N+1 queries, replace OFFSET pagination with cursor-based pagination, optimize aggregations, and batch INSERT/UPDATE operations. - Use Case: A dashboard endpoint takes 8 seconds because it runs a correlated subquery per row; rewrite it as a JOIN with aggregation, add a composite index on (user_id, status), and verify the improvement with EXPLAIN ANALYZE. ## Quick Start Analyze this slow PostgreSQL query with EXPLAIN ANALYZE and recommend indexes and rewrites to make it faster.

Frequently Asked Questions about sql-optimization-patterns

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

FAQPage Schema

How do I fix a slow SQL query in PostgreSQL?▼

Run EXPLAIN ANALYZE on the query to see actual execution times, scan types, and join methods. Look for sequential scans on large tables, then add appropriate indexes, rewrite functions in WHERE clauses, and filter rows before joining.

How to read EXPLAIN ANALYZE output in PostgreSQL?▼

Focus on scan type (Seq Scan vs Index Scan), estimated versus actual rows, cost, and actual time per node. Large row estimate mismatches indicate stale statistics, which running ANALYZE on the table can fix.

What index type should I use for my query?▼

Use B-Tree for equality and range queries, GIN for full-text search and JSONB, and partial or covering indexes for filtered subsets. Composite index column order must match your query's filter and sort patterns.

Why is my index not being used by the query planner?▼

Common causes include functions wrapping the column in WHERE, implicit type conversion, leading-wildcard LIKE patterns, and stale table statistics. Create a functional index matching the expression or rewrite the predicate to match the index.

When should I avoid adding more indexes?▼

Avoid over-indexing because every index slows down INSERT, UPDATE, and DELETE operations and consumes storage. Check pg_stat_user_indexes for indexes with zero scans and drop unused ones.

How do I fix N+1 query problems in my application?▼

Replace per-row queries with a single JOIN or a batched query using WHERE id IN (...). Then group the results by the parent key in application code to reconstruct the original object graph.