sql-optimization-patterns

Analyze SQL queries with EXPLAIN plans and indexing strategies.

1|Updated Jul 24, 2025

Search Tags:#performance #indexing #optimization #sql #best-practices #explain #query-plans

Ready to run

npx skills add https://github.com/civictechdc/votecatcher --skill sql-optimization-patterns-civictechdc

💬 Prompt for ChatGPT / Claude Chat▼

Please help me install this Agent Skill.
Skill: sql-optimization-patterns
Source: https://github.com/civictechdc/votecatcher/tree/main/backend/.agent/skills/sql-optimization-patterns
Command: npx skills add https://github.com/civictechdc/votecatcher --skill sql-optimization-patterns-civictechdc

SYSTEM DOCUMENTATION & REQUIREMENTS

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

What problem does it solve?

Slow SQL queries drain application performance and waste resources. This skill helps developers dramatically improve database performance by applying EXPLAIN analysis, indexing strategies, and query-pattern improvements to replace slow queries with efficient executions.

Core Features & Use Cases

EXPLAIN plan analysis to identify bottlenecks and guide targeted optimizations.
Comprehensive indexing strategies, including composite, partial, and expression indexes.
Query optimization patterns such as avoiding SELECT *, writing efficient JOINs, and implementing effective pagination.
Advanced techniques like materialized views, partitioning, and batch operations for large workloads.

Quick Start

Run an EXPLAIN ANALYZE on a slow query, identify the bottleneck, and implement an indexing or rewriting plan to improve performance.

Frequently Asked Questions about sql-optimization-patterns

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

FAQPage Schema

How do I use EXPLAIN plans to optimize slow SQL queries?▼

SQL query optimization with EXPLAIN plans involves running EXPLAIN ANALYZE to identify execution bottlenecks like full table scans. This isolates slow operations, guiding targeted indexing or query rewriting to achieve efficient database performance.

What are the best SQL indexing strategies for improving database performance?▼

The best SQL indexing strategies for database performance include using composite, partial, and expression indexes. These targeted indexing approaches minimize disk I/O by ensuring queries retrieve data through optimized lookup paths rather than scanning entire tables.

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

Yes, this SQL optimization approach works with PostgreSQL, MySQL, and other relational databases. The optimization patterns apply EXPLAIN plan analysis and indexing strategies universally to improve query performance across different relational database systems.

How do I write efficient JOINs and implement effective pagination in SQL?▼

Writing efficient JOINs and implementing effective pagination in SQL requires applying query-pattern improvements. These optimization patterns avoid SELECT * and restructure data retrieval, minimizing memory overhead and ensuring stable performance across large result sets.

When should I use materialized views or partitioning for large database workloads?▼

Use materialized views and partitioning for large database workloads when standard SQL optimization patterns are insufficient. These advanced techniques batch operations and segment data physically, delivering significant performance improvements for high-volume relational databases.