query-optimization

What problem does it solve?

This Skill provides a systematic approach to optimizing database queries, addressing issues like slow response times, inefficient data access, and resource contention. It helps developers identify and resolve performance bottlenecks, ensuring applications remain fast and scalable.

Core Features & Use Cases

• EXPLAIN Analysis: Guides on using EXPLAIN ANALYZE to understand query execution plans and identify performance hotspots.
• Index Creation: Provides patterns for creating effective single-column, composite, partial, and expression indexes to accelerate data retrieval.
• N+1 Query Prevention: Explains and solves the N+1 problem using SQLAlchemy's joinedload and selectinload for eager loading.
• Query Performance Monitoring: Shows how to leverage database statistics (e.g., pg_stat_statements) to find and prioritize slow queries.
• Efficient SQL Patterns: Guides on writing optimized SQL for SELECT, JOIN, WHERE, and ORDER BY clauses.
• Use Case: A backend developer notices that a page loading user orders is very slow. This skill helps them use EXPLAIN ANALYZE to inspect the query, identify an N+1 problem when fetching related user data, and then apply joinedload to resolve it, drastically improving page load times.

Quick Start

Analyze the SQL query SELECT * FROM users WHERE email = '[email protected]' using EXPLAIN ANALYZE to understand its execution plan.