query-optimization_skill

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

Python

2

GitHub Stars

1

Bundled Files

2 months ago

Catalog Refreshed

4 months ago

First Indexed

Readme & install

Copy the install command, review bundled files from the catalogue, and read any extended description pulled from the listing source.

Installation

Preview and clipboard use veilstrat where the catalogue uses aiagentskills.

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

SKILL.md998 B

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

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.