New: 98,000+ skills indexed from GitHub repos with 100+ stars · every skill safety-checkedBrowse top skillsTop skills
Esc to closeBrowse all skills
Submit a skillBrowse skills
Submit a skillBrowse skills

Agent skill

Analyzing Query Performance

by jeremylongshore in jeremylongshore/tons-of-skills-marketplace

Execute use when you need to work with query optimization. An agent skill from jeremylongshore/tons-of-skills-marketplace.

MITAuto-check passedDatabases

Install Analyzing Query Performance

skills CLI
$ npx skills add jeremylongshore/tons-of-skills-marketplace --skill analyzing-query-performance -a claude-code

Project install by default; add -g for ~/.claude/skills/.

GitHub CLI
$ gh skill install jeremylongshore/tons-of-skills-marketplace analyzing-query-performance --agent claude-code

Project scope by default; add --scope user for a personal install. Needs GitHub CLI 2.90.0 or later (public preview).

Manual copy
$ git clone --depth 1 https://github.com/jeremylongshore/tons-of-skills-marketplace.git skills-src && mkdir -p .claude/skills && cp -r skills-src/skills/.curated/analyzing-query-performance .claude/skills/analyzing-query-performance && rm -rf skills-src

Use ~/.claude/skills/ instead of .claude/skills for a personal install. The folder must contain SKILL.md.

Claude Code skills documentation · loads skills from .claude/skills/

Facts

Skill name
analyzing-query-performance
GitHub stars
2.8k
Token cost
~1.8k tokens
SKILL.md length
794 words
Files
4 (incl. scripts, references, assets)
Skills in repo
3,342
Repo updated
First seen
Licence
MIT

At a glance

Execute use when you need to work with query optimization. An agent skill from jeremylongshore/tons-of-skills-marketplace.

  • Works in 10 steps: Identify the slowest queries by… → Run EXPLAIN (ANALYZE, BUFFERS, FORMAT… → Analyze the execution plan for these red… → …
  • You need to work with query optimization
  • SKILL.md covers Overview, Prerequisites, Instructions and Output, plus 3 more sections
  • With phrases like optimize queries

What it does

Analyzing Query Performance is an agent skill from jeremylongshore/tons-of-skills-marketplace. Execute use when you need to work with query optimization. This skill provides query performance analysis with comprehensive guidance and automation. Trigger with phrases like "optimize queries", "analyze performance", or "improve query speed".

Its SKILL.md is about 1.8k tokens, which your agent loads only when the skill is triggered. The skill folder holds 6 other files, including scripts, reference files and assets (for example `assets/README.md`, `references/README.md` and `scripts/README.md`). Compatibility notes: Designed for Claude Code

It sits in Databases, covering Query optimization. It works with PostgreSQL, MySQL and MongoDB. The repository describes itself as: Model-agnostic agent-skills platform with a harness-free canonical layer, verified adapters, and the ccpi package manager. Explore at tonsofskills.com. The licence is MIT.

When your agent uses it

  • You need to work with query optimization
  • With phrases like optimize queries
  • Analyze performance
  • Improve query speed

Example prompts

  • “optimize queries”
  • “analyze performance”
  • “improve query speed”
  • “/analyzing-query-performance”

Requirements

  • Compatibility (from SKILL.md): Designed for Claude Code
  • Pre-approved tools (allowed-tools): Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*)

Workflow steps

10 steps, taken from the first numbered list in SKILL.md.

  1. Identify the slowest queries by examining pg_stat_statements (PostgreSQL): SELECT query, calls, mean_exec_time, total_exec_time FROM…
  2. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on each slow query in PostgreSQL, or EXPLAIN ANALYZE FORMAT=JSON in MySQL. Capture the full…
  3. Analyze the execution plan for these red flags
  4. Check buffer cache performance: SELECT heap_blks_read, heap_blks_hit, heap_blks_hit::float / (heap_blks_hit + heap_blks_read) AS…
  5. Evaluate index usage with SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE schemaname = 'public'…
  6. Check for table bloat using SELECT relname, n_live_tup, n_dead_tup, n_dead_tup::float / GREATEST(n_live_tup, 1) AS dead_ratio FROM…
  7. For each identified issue, generate a specific recommendation: CREATE INDEX statement with the exact columns, query rewrite suggestions…
  8. Estimate the performance impact of each recommendation by comparing the EXPLAIN plan before and after applying the change on a staging…
  9. Prioritize recommendations by impact-to-effort ratio: index additions (high impact, low effort) before query rewrites (medium impact…
  10. Generate a performance analysis report with before/after execution plans, estimated improvements, and implementation priority ranking.

What it can do on your machine

Read from SKILL.md and the folder at commit 80f86df. It shows what the files ask for, not the result of running them.

  • Tool permissions

    Pre-approves these tools, so the agent can use them without asking each time:

    • Read
    • Write
    • Edit
    • Grep
    • Glob
    • Bash(psql:*)
    • Bash(mysql:*)
    • Bash(mongosh:*)

    From allowed-tools in the SKILL.md frontmatter.

  • Runs code

    Ships 1 file in scripts/, which the agent can run.

    From the folder's file list and the shell code blocks in SKILL.md.

  • Network

    Links to these hosts (documentation or services it may open):

    • postgresql.org
    • use-the-index-luke.com
    • pgmustard.com

    From URLs in SKILL.md, links to its own repository left out.

  • Credentials

    Names no API keys, tokens, secrets or passwords.

    From names ending in _API_KEY, _TOKEN, _SECRET, _KEY or _PASSWORD in SKILL.md.

  • Compatibility

    Designed for Claude Code

    From compatibility in the SKILL.md frontmatter.

Context cost

Analyzing Query Performance loads about 1.8k tokens when it runs, and up to ~1.8k if it reads all its reference files. Until then it costs about 68 tokens; SKILL.md has 794 words of instructions outside code blocks.

Always · name and description, kept in context so the agent knows when to use it
~68
When it runs · the whole SKILL.md, loaded when a task matches
~1.8k
With references · SKILL.md plus every file in references/, read only if the agent opens them
~1.8k

Estimates: characters ÷ 4, the usual rule of thumb; real counts depend on the model's tokenizer. Scripts and assets cost tokens only if the agent reads them.

Safety

Auto-check passed

The automated check found no risky patterns in SKILL.md.

Automated static check — not a guarantee. Review scripts before installing. It scans the text of SKILL.md for risky patterns (piping downloads into a shell, reading credential files, hidden Unicode, destructive commands); the scripts in this folder are not scanned.

SKILL.md

The full file from jeremylongshore/tons-of-skills-marketplace at commit 80f86df, republished under its MIT licence (© jeremylongshore). 794 words, ~1,776 tokens.

Download SKILL.mdSave it as .claude/skills/analyzing-query-performance/SKILL.md (or your agent's skills folder). This skill also uses 3 other files; get the full folder from GitHub.
name
analyzing-query-performance
description
Execute use when you need to work with query optimization. This skill provides query performance analysis with comprehensive guidance and automation. Trigger with phrases like "optimize queries", "analyze performance", or "improve query speed".
allowed-tools
Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*)
compatibility
Designed for Claude Code
version
1.27.0
author
Jeremy Longshore <jeremy@intentsolutions.io>
license
MIT
tags
database, performance, analyzing-query

Query Performance Analyzer

Overview

Analyze slow database queries using execution plans, wait statistics, and I/O metrics across PostgreSQL, MySQL, and MongoDB. This skill captures EXPLAIN output, identifies sequential scans on large tables, detects missing indexes, measures buffer cache hit ratios, and produces actionable optimization recommendations ranked by expected performance impact.

Prerequisites

  • Database credentials with permissions to run EXPLAIN ANALYZE (PostgreSQL), EXPLAIN FORMAT=JSON (MySQL), or explain() (MongoDB)
  • pg_stat_statements extension enabled for PostgreSQL (provides aggregated query statistics)
  • Access to slow query logs or performance_schema (MySQL)
  • Baseline query execution times for comparison
  • psql, mysql, or mongosh CLI tools installed

Instructions

  1. Identify the slowest queries by examining pg_stat_statements (PostgreSQL): SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 20. For MySQL, enable and query the slow query log or performance_schema.events_statements_summary_by_digest.

  2. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on each slow query in PostgreSQL, or EXPLAIN ANALYZE FORMAT=JSON in MySQL. Capture the full execution plan including actual row counts, loop iterations, and buffer usage.

  3. Analyze the execution plan for these red flags:

    • Sequential scans on tables with >10,000 rows (indicates missing index)
    • Nested loop joins with high outer row counts (consider hash join or merge join)
    • Sort operations without index support (adding a covering index eliminates the sort)
    • High rows_removed_by_filter relative to rows (predicate not selective enough)
    • Bitmap heap scans with high recheck rate (index selectivity too low)

  4. Check buffer cache performance: SELECT heap_blks_read, heap_blks_hit, heap_blks_hit::float / (heap_blks_hit + heap_blks_read) AS cache_hit_ratio FROM pg_statio_user_tables WHERE relname = 'table_name'. A ratio below 0.95 suggests the working set exceeds available shared_buffers.

  5. Evaluate index usage with SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE schemaname = 'public' ORDER BY idx_scan ASC. Indexes with zero scans are unused and waste write performance.

  6. Check for table bloat using SELECT relname, n_live_tup, n_dead_tup, n_dead_tup::float / GREATEST(n_live_tup, 1) AS dead_ratio FROM pg_stat_user_tables WHERE n_dead_tup > 1000 ORDER BY dead_ratio DESC. A dead tuple ratio above 0.2 indicates the table needs VACUUM.

  7. For each identified issue, generate a specific recommendation: CREATE INDEX statement with the exact columns, query rewrite suggestions, or configuration parameter adjustments.

  8. Estimate the performance impact of each recommendation by comparing the EXPLAIN plan before and after applying the change on a staging database or by analyzing the expected row reduction from new indexes.

  9. Prioritize recommendations by impact-to-effort ratio: index additions (high impact, low effort) before query rewrites (medium impact, medium effort) before schema changes (high impact, high effort).

  10. Generate a performance analysis report with before/after execution plans, estimated improvements, and implementation priority ranking.

Output

  • Slow query inventory with execution frequency, mean/P95 duration, and total time consumed
  • Annotated execution plans highlighting sequential scans, sort bottlenecks, and join inefficiencies
  • Index recommendations as ready-to-execute CREATE INDEX statements with expected impact
  • Query rewrite suggestions with original and optimized SQL side by side
  • Buffer cache analysis with shared_buffers sizing recommendations
  • Performance report ranking all findings by severity and implementation priority
Show full SKILL.md (313 more words)Show less

Error Handling

ErrorCauseSolution
EXPLAIN ANALYZE takes too long on productionQuery modifies data or runs for minutesUse EXPLAIN without ANALYZE for estimated plans; run EXPLAIN ANALYZE on staging with representative data
pg_stat_statements not availableExtension not installed or not in shared_preload_librariesRun CREATE EXTENSION pg_stat_statements; add to shared_preload_libraries in postgresql.conf and restart
Execution plan differs between staging and productionDifferent data distribution, statistics, or configurationRun ANALYZE on staging tables to update statistics; match work_mem, random_page_cost, and effective_cache_size settings
Index recommendation causes slow writesToo many indexes on a write-heavy tableLimit indexes to 5-7 per table; use partial indexes to reduce scope; consider covering indexes to replace multiple single-column indexes
Query plan uses wrong indexStale statistics or cost model miscalculationRun ANALYZE table_name to refresh statistics; adjust random_page_cost for SSD storage; use SET enable_seqscan = off to test index plans

Examples

Optimizing a dashboard aggregate query: A query computing daily revenue with GROUP BY date and JOIN across orders and line_items takes 12 seconds. EXPLAIN reveals a sequential scan on line_items (5M rows). Adding a composite index on (order_id, created_at) with INCLUDE (amount) reduces execution to 200ms by enabling an index-only scan.

Diagnosing N+1 query pattern: Application loads a list page showing 50 products, each with a separate query for category name. pg_stat_statements reveals SELECT name FROM categories WHERE id = $1 called 50 times per page load. Resolution: rewrite as a single JOIN query or implement eager loading in the ORM.

Identifying bloated table causing cache misses: Buffer cache hit ratio drops to 0.78 on the sessions table. Investigation reveals 80% dead tuples due to aggressive INSERT/DELETE cycling without autovacuum tuning. Setting autovacuum_vacuum_scale_factor = 0.01 and running VACUUM FULL restores cache hit ratio to 0.99.

Resources

© jeremylongshore, MIT. Rendered from Markdown: HTML in the file is shown as text, images as links, and headings moved down two levels. Raw file

Files

SKILL.md and 3 other files (scripts, references, assets) in skills/.curated/analyzing-query-performance of jeremylongshore/tons-of-skills-marketplace.

  • SKILL.md
  • assets/README.md
  • references/README.md
  • scripts/README.md

Open the folder on GitHubat commit 80f86df

Compare with similar skills

Analyzing Query Performance next to the 5 skills that share the most tags, products or categories with it. Stars are the repository's; “used in” counts other GitHub owners with a copy.

Analyzing Query Performance compared with similar skills
SkillStarsUsed inTokensAuto-checkLicenceRepo updated
Analyzing Query Performance this skilljeremylongshore/tons-of-skills-marketplace2.8k—~1.8kAutomated safety check: PassMIT
Query Expertjamesrochabrun/skills216—~4.3kAutomated safety check: PassMIT
Database Testingpetrkindlmann/qa-skills168—~4.2kAutomated safety check: PassMIT
How To Communicatedatabasus/databasus8.8k—~3.9kAutomated safety check: PassMIT
Sql2erystemsrx/sql_to_ER188—~1.1kAutomated safety check: PassAGPL-3.0
Prisma Database Setupcurvenote/curvenote1703 repos~1.4kAutomated safety check: PassMIT

Similar skills

  • Query Expert

    jamesrochabrun/skills

    Master SQL and database queries across multiple systems. An agent skill from jamesrochabrun/skills.

    216 GitHub stars~4.3k tokensUpdated 8 mo ago
    DatabasesAuto-check passed

  • Database Testing

    petrkindlmann/qa-skills

    Validate database integrity, test migrations forward and backward, verify schema constraints, manage seed data, detect migration drift, and identify query performance issues.

    168 GitHub stars~4.2k tokensUpdated 4 mo ago
    DatabasesAuto-check passed

  • How To Communicate

    databasus/databasus

    Communicate clearly in every response, progress update and agent-authored document.

    8.8k GitHub stars~3.9k tokensUpdated 17 days ago
    DatabasesAuto-check passed

  • Sql2er

    ystemsrx/sql_to_ER

    A skill your agent uses when the user wants a Chen-model ER diagram from SQL CREATE TABLE statements or DBML, wants to rearrange or clean up an existing sql2er state, wants a skeleton-only overview…

    188 GitHub stars~1.1k tokensUpdated 9 days ago
    DatabasesAuto-check passed

  • Prisma Database Setup

    curvenote/curvenote

    Guides for configuring Prisma with different database providers (PostgreSQL, MySQL, SQLite, MongoDB, etc.).

    170 GitHub starsUsed in 3 repos~1.4k tokens
    DatabasesAuto-check passed

  • DB Ops Sop

    OpenDCAI/DataMind

    Database operations runbook — backup, recovery, performance tuning, troubleshooting.

    449 GitHub stars~388 tokensUpdated 20 days ago
    DatabasesAuto-check passed

More from jeremylongshore/tons-of-skills-marketplace

All 3,342 skills in this repo

  • Performing Security Code Review

    jeremylongshore/tons-of-skills-marketplace

    Execute this skill enables AI assistant to conduct a security-focused code review using the security-agent plugin.

    2.8k GitHub starsUsed in 2 repos~1.3k tokens
    Auto-check: notes

  • Adapting Transfer Learning Models

    jeremylongshore/tons-of-skills-marketplace

    Build this skill automates the adaptation of pre-trained machine learning models using transfer learning techniques.

    2.8k GitHub stars~1.1k tokensUpdated yesterday
    Auto-check passed

  • Agent Context Loader

    jeremylongshore/tons-of-skills-marketplace

    Execute proactive auto-loading: automatically detects and loads agents.md files.

    2.8k GitHub stars~1.1k tokensUpdated yesterday
    Auto-check passed

  • Aggregating Performance Metrics

    jeremylongshore/tons-of-skills-marketplace

    Aggregate and centralize performance metrics from applications, systems, databases, caches, and services.

    2.8k GitHub stars~1.2k tokensUpdated yesterday
    Auto-check passed

  • Analyzing Capacity Planning

    jeremylongshore/tons-of-skills-marketplace

    Execute this skill enables AI assistant to analyze capacity requirements and plan for future growth.

    2.8k GitHub stars~947 tokensUpdated yesterday
    Auto-check passed

  • Analyzing Database Indexes

    jeremylongshore/tons-of-skills-marketplace

    Process use when you need to work with database indexing. An agent skill from jeremylongshore/tons-of-skills-marketplace.

    2.8k GitHub stars~2k tokensUpdated yesterday
    Auto-check passed

Works with

Categories

Questions about Analyzing Query Performance

What does Analyzing Query Performance do?

Execute use when you need to work with query optimization. An agent skill from jeremylongshore/tons-of-skills-marketplace. Analyzing Query Performance is an agent skill from jeremylongshore/tons-of-skills-marketplace. Execute use when you need to work with query optimization.

When should I use Analyzing Query Performance?

Analyzing Query Performance fits situations like: you need to work with query optimization; with phrases like optimize queries; analyze performance; improve query speed.

How do I install Analyzing Query Performance in Claude Code?

Run `npx skills add jeremylongshore/tons-of-skills-marketplace --skill analyzing-query-performance -a claude-code`. Or copy the skill folder (skills/.curated/analyzing-query-performance in jeremylongshore/tons-of-skills-marketplace) into .claude/skills/analyzing-query-performance in your project. Claude Code loads it when a task matches its description.

How do I install Analyzing Query Performance in Codex?

Run `npx skills add jeremylongshore/tons-of-skills-marketplace --skill analyzing-query-performance -a codex`. Or copy the skill folder (skills/.curated/analyzing-query-performance in jeremylongshore/tons-of-skills-marketplace) into .agents/skills/analyzing-query-performance in your project. Codex loads it when a task matches its description.

Can I use Analyzing Query Performance in Cursor, Gemini CLI or GitHub Copilot?

Cursor, Gemini CLI, GitHub Copilot and OpenCode also load SKILL.md folders. With the skills CLI, run `npx skills add jeremylongshore/tons-of-skills-marketplace --skill analyzing-query-performance -a cursor` (or -a gemini-cli, github-copilot or opencode for the others). To copy it by hand, put the folder in .cursor/skills/analyzing-query-performance, .gemini/skills/analyzing-query-performance, .github/skills/analyzing-query-performance and .opencode/skills/analyzing-query-performance in your project.

What does Analyzing Query Performance need to run?

SKILL.md names no scripts, command-line tools or credentials: Analyzing Query Performance is instructions for the agent only. Its frontmatter pre-approves these tools: Read, Write, Edit, Grep, Glob, Bash(psql:*), Bash(mysql:*), Bash(mongosh:*). Compatibility (from SKILL.md): Designed for Claude Code.

Does Analyzing Query Performance access the network?

SKILL.md names 3 domains. As links in the text: postgresql.org, use-the-index-luke.com and pgmustard.com. This is read from the text; nothing was executed.

Is Analyzing Query Performance safe to install?

Our automated static check of SKILL.md found no risky patterns, such as piping downloads into a shell, reading credential files or hidden Unicode. It is not a guarantee. The check reads SKILL.md only: the scripts in the folder are not scanned, so read them before running anything.

What licence does Analyzing Query Performance use?

Analyzing Query Performance is published under the MIT licence (declared in SKILL.md). It allows redistribution, so the full SKILL.md is shown on this page.

How many tokens does Analyzing Query Performance use?

About 1.8k tokens (SKILL.md is roughly 7.1k characters). Agents keep only the skill's name and description in context until a task matches; then they load SKILL.md in full. Its references folder adds about 18 tokens, read only when the agent opens those files.

What are the alternatives to Analyzing Query Performance?

Skills that share tags, products or a category with Analyzing Query Performance: Query Expert (jamesrochabrun/skills, 216 stars), Database Testing (petrkindlmann/qa-skills, 168 stars), How To Communicate (databasus/databasus, 8.8k stars) and Sql2er (ystemsrx/sql_to_ER, 188 stars). The comparison table on this page puts their stars, adoption, token cost, safety result and licence side by side.

Who maintains Analyzing Query Performance?

jeremylongshore (a GitHub user) maintains it in jeremylongshore/tons-of-skills-marketplace, which has 2,825 GitHub stars. The repository holds 3,342 skills in this directory. The repository was last updated on October 9, 2026.

Source: jeremylongshore/tons-of-skills-marketplace on GitHub. Facts on this page come from the repository at the commit we read; the author's words are quoted as theirs.