home / skills / pluginagentmarketplace / custom-plugin-sql / query-optimization

query-optimization skill

This skill analyzes SQL queries, suggests indexing and rewriting techniques to boost performance across databases.

npx playbooks add skill pluginagentmarketplace/custom-plugin-sql --skill query-optimization

Review the files below or copy the command above to add this skill to your agents.

Files (6)

SKILL.md

998 B

---
name: query-optimization
description: SQL query optimization and performance tuning
sasmp_version: "1.3.0"
bonded_agent: 01-sql-fundamentals
bond_type: PRIMARY_BOND
---

# Query Optimization Skill

## Overview
Optimize SQL queries for maximum performance across different database systems.

## Topics Covered

### Query Analysis
- EXPLAIN plans
- Execution statistics
- Cost-based optimization
- Query profiling
- Wait event analysis

### Indexing Strategy
- B-tree indexes
- Covering indexes
- Partial indexes
- Index selection
- Index maintenance

### Query Patterns
- JOIN optimization
- Subquery alternatives
- Aggregate optimization
- Pagination patterns
- Bulk operations

### Database Specific
- PostgreSQL optimization
- MySQL tuning
- SQL Server hints
- Oracle optimization
- Cloud DB optimization

## Prerequisites
- SQL fundamentals
- Database internals basics

## Learning Outcomes
- Analyze query plans
- Create effective indexes
- Rewrite slow queries
- Tune database performance

Overview

This skill provides practical SQL query optimization and performance tuning guidance for relational databases. It focuses on analyzing execution plans, selecting and maintaining indexes, and applying query rewrite patterns to reduce latency and resource usage. The content spans common engines including PostgreSQL, MySQL, SQL Server, and Oracle, with cloud-specific considerations.

How this skill works

The skill inspects query execution plans, runtime statistics, and wait events to identify bottlenecks and expensive operations. It recommends index strategies (B-tree, covering, partial), join and aggregation rewrites, and configuration or hint-based adjustments for specific database engines. Actionable steps include profiling queries, validating changes with explain plans, and measuring before/after performance.

When to use it

When queries exhibit high latency or unpredictable runtime
To reduce CPU, I/O, or memory consumption on the database server
Before adding hardware — to get more from existing resources
When schema changes are planned and index impact must be evaluated
When migrating or tuning workloads for cloud databases

Best practices

Start with EXPLAIN/EXPLAIN ANALYZE and baseline metrics before making changes
Prefer query rewrites and proper indexing before applying hints or engine-specific hacks
Use covering and partial indexes to limit I/O for frequent access patterns
Test performance changes in a staging environment with representative data
Monitor index maintenance cost; avoid over-indexing that slows writes

Example use cases

Convert correlated subqueries to joins or use window functions to improve performance
Design a covering index to eliminate expensive table lookups for a reporting query
Tune pagination by replacing OFFSET with keyset pagination for large result sets
Profile and optimize bulk INSERT/UPDATE operations by batching and disabling nonessential indexes temporarily
Apply PostgreSQL-specific configuration and vacuum strategies to reduce bloat and improve planner estimates

FAQ

Do I need engine-specific knowledge to use this skill?

Basic engine differences are covered, but core techniques like plan analysis and indexing apply across engines. Engine-specific advice is included for deeper tuning.

How do I validate that an optimization is safe?

Validate by comparing EXPLAIN ANALYZE outputs, running tests on staging with representative data, and ensuring query results remain identical before deploying to production.