sql-optimization-patterns

Overview of sql-optimization-patterns skill

What sql-optimization-patterns does

The sql-optimization-patterns skill helps an agent diagnose slow SQL, explain why a query is slow, and suggest concrete fixes around query shape, indexing, joins, pagination, and execution-plan reading. It is most useful when you already have a real performance problem and want a structured optimization workflow instead of generic “add an index” advice.

Who should install it

This skill fits backend engineers, database engineers, platform teams, and application developers who need faster queries without guessing. It is especially relevant for teams working with PostgreSQL-style EXPLAIN output, but the reasoning patterns also transfer to other relational databases.

Real job-to-be-done

Users typically need more than a syntax review. They need to:

• identify why a query is slow
• map plan symptoms to likely root causes
• choose between query rewrite and index changes
• avoid “fixes” that speed one query up while hurting writes or storage
• communicate tradeoffs clearly to a team

That is where sql-optimization-patterns is more useful than a plain prompt.

What makes this skill different

The main differentiator is its pattern-oriented framing. It does not just say “optimize SQL”; it pushes the agent toward practical database engineering concerns such as:

• reading EXPLAIN and EXPLAIN ANALYZE
• spotting sequential scans, bad join choices, and inefficient pagination
• choosing index strategies intentionally
• handling common anti-patterns like N+1 access patterns

Best-fit and misfit cases

Best fit:

• a known slow query
• a query plan you can paste in
• schema details, indexes, and row-count context available
• performance work tied to API latency or database load

Weaker fit:

• no query text
• no execution plan
• purely ORM-level architecture questions with no SQL visibility
• vendor-specific tuning that depends on engine internals not provided in the prompt

How to Use sql-optimization-patterns skill

Install context for sql-optimization-patterns

The repository does not surface a custom installer inside SKILL.md, so install it through your skill-loading workflow for the wshobson/agents repository, then make sure your agent can access the skill named sql-optimization-patterns.

If your environment uses the common Skills flow, the practical pattern is to add the repository and then invoke the skill by name in a task that clearly asks for SQL optimization analysis.

Read this file first

Start with:

• plugins/developer-essentials/skills/sql-optimization-patterns/SKILL.md

This skill is self-contained. There are no supporting references/, rules/, or scripts in the skill folder, so most of the usable guidance is in that single file. That is good for fast adoption, but it also means the quality of your input matters more.

What input the skill needs

For strong output, give the agent:

• the SQL query
• database engine and version if known
• table schemas or relevant columns
• existing indexes
• row-count scale or rough table sizes
• the EXPLAIN or EXPLAIN ANALYZE output
• the business goal, such as “reduce P95 endpoint latency”

Without plan data, the agent can still suggest patterns, but the advice will be less trustworthy.

Minimum viable prompt

A workable sql-optimization-patterns usage prompt looks like this:

Use the sql-optimization-patterns skill.

Database: PostgreSQL 15
Goal: reduce this query from 2.4s to under 200ms
Tables:
- users(id, email, created_at, status)
- orders(id, user_id, order_total, created_at)

Current indexes:
- users_pkey(id)
- orders_user_id_idx(user_id)

Query:
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days'
ORDER BY u.created_at DESC
LIMIT 100;

EXPLAIN ANALYZE:
[paste full plan]

Please identify likely bottlenecks, explain the plan, recommend query/index changes, and rank fixes by expected impact and risk.

How to turn a rough request into a strong one

Weak request:

Make this SQL faster.

Strong request:

Use sql-optimization-patterns for Database Engineering.

I need:
1. plan interpretation
2. likely root cause
3. index recommendations with rationale
4. query rewrite options
5. tradeoffs for write amplification and storage
6. a safe rollout order

Engine: PostgreSQL 14
Traffic pattern: read-heavy API endpoint
Data scale: users 8M rows, orders 120M rows
Current problem: endpoint P95 is 1.8s, DB CPU spikes during peak
Query: [paste]
Plan: [paste]
Existing indexes: [paste]

The second prompt works better because it gives the skill enough operational context to prioritize fixes rather than listing generic tips.

Practical workflow that gets the best results

A good workflow is:

1. paste the exact query and full plan
2. ask the agent to explain the biggest cost drivers in plain English
3. ask for 2-3 candidate fixes, not 10
4. request expected upside and downside for each
5. implement the lowest-risk change first
6. rerun EXPLAIN ANALYZE
7. compare before and after

This keeps the skill grounded in measurable outcomes instead of speculative tuning.

What the skill is especially good at

The source material strongly emphasizes:

• EXPLAIN interpretation
• index strategy
• join-pattern analysis
• pagination efficiency
• N+1 query issues

That means it is a strong fit for query-level optimization and schema/index decisions, not just SQL formatting.

What to ask for explicitly

To get more useful output from sql-optimization-patterns install and usage efforts, ask the agent for:

• “the single biggest bottleneck in this plan”
• “whether a new index or a query rewrite is the better first move”
• “which scan/join types are hurting me”
• “whether the query can become index-only”
• “how this change affects writes, vacuum, or storage”
• “what to measure after rollout”

These prompts force prioritization, which is often the difference between helpful and noisy output.

Common blockers during adoption

The biggest blockers are usually not installation issues but missing evidence:

• only partial plan output
• no table size information
• no current indexes listed
• ORM-generated SQL pasted without business context
• asking for “best practices” instead of diagnosing a real path

If you only provide query text, expect broad suggestions. If you provide plan, schema, and workload context, the skill becomes much more decision-useful.

Interpreting the output safely

Do not treat every suggested index as a free win. A good sql-optimization-patterns guide mindset is to validate:

• whether the filter or join predicates actually match the proposed index order
• whether the query is selective enough to benefit
• whether a composite index is better than multiple single-column indexes
• whether the fix helps one query but harms insert/update performance

The skill is strongest when used to generate hypotheses you can test immediately.

sql-optimization-patterns skill FAQ

Is sql-optimization-patterns worth installing if I already know SQL?

Yes, if you want consistent optimization analysis under pressure. The value is not basic SQL knowledge; it is the structured pattern set around plans, scan types, joins, and index choices.

Is this skill beginner-friendly?

Reasonably, but beginners should use it with real examples. The skill becomes much clearer when you compare the query text with EXPLAIN ANALYZE output. If you are new to execution plans, ask the agent to define every important node in plain language before recommending changes.

How is this better than a normal AI prompt?

A normal prompt often gives generic tuning tips. The sql-optimization-patterns skill is oriented toward a repeatable optimization workflow and the practical signals that matter in database engineering, especially plan interpretation and index strategy.

Does it require PostgreSQL?

No, but the examples are clearly PostgreSQL-flavored. If you use MySQL, SQLite, SQL Server, or another engine, include the engine name and equivalent plan output so the agent can adapt the guidance.

When should I not use sql-optimization-patterns?

Do not start here if the main issue is:

• connection pooling
• lock contention with no query evidence
• infrastructure saturation outside the database
• missing application caching strategy
• ORM misuse that hides the actual SQL

In those cases, this skill may help later, but it is not the first tool.

Can it help with schema design, not just one slow query?

Yes. The skill covers indexing strategy and scalable query patterns, so it can support schema decisions. It is still most effective when tied to access patterns, expected filters, joins, sort orders, and data volume.

How to Improve sql-optimization-patterns skill

Give sql-optimization-patterns better evidence

The fastest way to improve results is to provide:

• full query text, not a paraphrase
• full plan output, not just the top few lines
• actual timing from EXPLAIN ANALYZE when safe
• existing indexes
• approximate cardinality and row counts
• whether the workload is read-heavy or write-heavy

Better evidence produces more credible recommendations and fewer irrelevant indexes.

Ask for ranked recommendations

The skill is more useful when you ask it to rank fixes by:

• expected latency impact
• implementation effort
• operational risk
• write overhead
• reversibility

That ranking helps teams choose an action, not just collect ideas.

Force tradeoff analysis

One common failure mode is accepting a fast-read solution that creates expensive writes or bloated indexes. Improve sql-optimization-patterns output by asking:

• “What does this index cost on inserts and updates?”
• “Will this create redundant indexes?”
• “Would partitioning or a rewrite be better than another index?”
• “Is keyset pagination better here than offset pagination?”

Use before-and-after iteration

After the first recommendation, rerun the plan and ask the skill to compare:

• original bottleneck
• new bottleneck
• what improved
• what remains expensive
• whether the next optimization is still worth doing

This is the most reliable way to turn the skill into an optimization loop rather than a one-shot answer.

Watch for common failure modes

The most common mistakes are:

• optimizing without ANALYZE data
• adding indexes for low-selectivity filters
• ignoring join order and cardinality estimates
• rewriting SQL without validating the new plan
• focusing on one query while missing an N+1 pattern upstream

You can reduce these errors by asking the agent to state assumptions explicitly before suggesting changes.

Prompt patterns that improve output quality

Use prompts like:

Use the sql-optimization-patterns skill and do not give generic advice.
Base recommendations on the query plan provided.
For each proposed fix, explain:
1. why it should help
2. what plan node it targets
3. what tradeoff it introduces
4. how to validate it after deployment

That format pushes the agent to connect each suggestion to evidence.

Improve team adoption

If you want this skill to help a team, standardize the input template:

• engine/version
• query
• schema
• indexes
• plan
• table sizes
• target latency
• workload notes

This reduces variability and makes sql-optimization-patterns for Database Engineering much more repeatable across incidents and code reviews.