Guide · Agent skill
How to use the SQL Optimization Patterns agent skill
Updated · written with AI from the project's own documentation and checked against our catalogue data
The sql-optimization-patterns skill is a reference guide that an AI agent loads so it can help you make slow PostgreSQL queries faster. It covers reading EXPLAIN query plans, choosing index types, rewriting common query patterns and running monitoring queries. This guide explains what the skill does, how to install it, how to ask for help with natural requests, and what to watch out for.
SQL Optimization Patternswshobson/agents · MIT Download the skill Quick start
- 1Download and unzip. Download the skill ZIP from this page and unzip it so you have a folder containing SKILL.md and references/details.md.
- 2Place the folder. Move the folder to ~/.claude/skills/ for personal use or to your project's .claude/skills/ folder for one project.
- 3Or upload it in the Claude apps. In the Claude apps, open Settings → Capabilities → Skills and upload the ZIP.
- 4Connect a PostgreSQL database. Give the agent access to a PostgreSQL database for running EXPLAIN and monitoring queries, preferably a staging or read-only copy.
- 5Ask a specific question. Describe the slow query or schema and include the SQL and EXPLAIN ANALYZE output if you have it.
- 6Review suggestions. Read each suggested index or rewrite and test it before applying it to production.
What an agent skill is and what this one makes the agent do
An agent skill is a folder with a SKILL.md file that gives an AI agent specialised instructions it can load when a task matches. This skill is written as reference knowledge. Once it is active, the agent knows how to explain PostgreSQL EXPLAIN output, which index types to consider, and which query rewrites to suggest.
- Explains how to read PostgreSQL EXPLAIN plans, including Seq Scan, Index Scan, Index Only Scan and the join methods.
- Describes index types: B-Tree, Hash, GIN, GiST and BRIN, with examples of standard, composite, partial, expression, covering, full-text and JSONB indexes.
- Suggests query rewrites such as avoiding SELECT *, avoiding functions in WHERE clauses and filtering before joining.
- Includes monitoring queries that find slow statements, tables with many sequential scans and unused indexes.
- Covers the N+1 query problem and fixes using JOINs or batch loading.
Install the skill from a ZIP file
Download the ZIP for this skill from this page and unzip it. The folder should contain SKILL.md and references/details.md. For a personal install, place the folder in ~/.claude/skills/. For a single project, place it in the project's .claude/skills/ folder so it applies only to that codebase.
- Personal install: put the skill folder in ~/.claude/skills/.
- Project install: put the skill folder in your project's .claude/skills/ folder.
- Claude apps: upload the ZIP under Settings → Capabilities → Skills.
Trigger the skill with natural requests
You do not need a special command. The skill's description says it applies when debugging slow queries, designing database schemas or optimizing application performance, so ask for those things in plain language and include the relevant SQL, EXPLAIN output or table definitions.
- Why is this query slow? Here is the EXPLAIN ANALYZE output: ...
- Review this schema and suggest indexes for the queries we run most often.
- This endpoint runs one query per user. Can you find the N+1 problem and fix it?
- Which indexes on our orders table are unused and safe to review?
Run the EXPLAIN and monitoring queries
The skill includes EXPLAIN examples you can run yourself or ask the agent to run through a database tool you have connected. A typical starting point is EXPLAIN ANALYZE on the slow query, which shows actual execution times along with the plan.
- Look for Seq Scan on large tables and check whether a suitable index exists.
- Compare estimated rows with actual rows; large gaps can point to stale statistics.
- Run ANALYZE on tables whose statistics may be out of date.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
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';Find slow queries and unused indexes
The monitoring section lists queries for finding the statements that take the most time and indexes that are never scanned. The slow query query requires the pg_stat_statements extension, so check that it is installed before you rely on it. Note that the skill's examples use the total_time and mean_time column names, which older PostgreSQL versions use; newer versions name these differently, so confirm the column names on your server before running them.
- Slow queries: uses pg_stat_statements, which must be enabled.
- Missing indexes candidates: tables with high seq_tup_read and seq_scan counts.
- Unused indexes: indexes with idx_scan = 0, sorted by size.
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;Review changes safely before applying them
The skill tells the agent what to look for and suggest. It does not run changes by itself. Review each suggested index or rewrite before applying it, and test on a copy of your data first. Some maintenance commands lock tables: VACUUM FULL reclaims space but locks the table, and new indexes can slow writes. Run changes in a staging environment or a maintenance window when you can.
- Start with plain VACUUM ANALYZE rather than VACUUM FULL unless you understand the locking.
- Check write performance after adding indexes, since each index slows INSERT, UPDATE and DELETE.
- Keep a rollback plan for index changes.
What people use it for
- Diagnosing a slow report query by reading its EXPLAIN ANALYZE output and finding a Seq Scan on a large table.
- Adding a partial index for a frequently filtered status column.
- Replacing an N+1 loop in an ORM-backed endpoint with a JOIN or batch query.
- Finding unused indexes on a write-heavy table to reduce insert and update cost.
- Planning indexes for a new schema before launch, including composite index column order.
Frequently asked questions
How do I install the sql-optimization-patterns skill?
Download the ZIP from this page and unzip it. Put the folder in ~/.claude/skills/ for personal use or in a project's .claude/skills/ folder, or upload the ZIP in the Claude apps under Settings → Capabilities → Skills.
Does this skill work with MySQL or SQL Server?
No. The skill is written for PostgreSQL. EXPLAIN syntax, index types and monitoring views differ on other databases, so some examples will not apply.
Does the skill run queries or scripts on my database?
No. The skill includes no scripts and uses no network. It is reference knowledge that tells the agent what to look for and suggest. Any queries run only if you or the agent run them through a database connection you provide.
What do I need to use the slow query monitoring?
You need PostgreSQL database access and the pg_stat_statements extension enabled. Without that extension, the slow query query in the skill will not return results.
Is the sql-optimization-patterns skill free?
The skill is released under the MIT licence, as listed on this page. Check the licence file in the source repository for the full terms.
Can the agent optimize my production database automatically?
No, and you should not let it. The skill does not make changes on its own. Some commands, such as VACUUM FULL, lock tables, so review and test suggestions before running them on production.
New agent skills, repos and AI tools — every week
The best new skills and open-source projects, the fastest-growing repos, and one free prompt. Five minutes to read. Unsubscribe anytime.
Our verdict
This skill suits backend developers and engineers who work on PostgreSQL and want an agent to explain query plans, suggest indexes and spot N+1 or full table scan problems. It is not useful for MySQL, SQL Server or other non-PostgreSQL databases, and anyone who wants fully automated changes to a live production database should look for a tool designed for that.
Download the skill