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 Database Indexes

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

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

MITAuto-check passedDatabases

Install Analyzing Database Indexes

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

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

GitHub CLI
$ gh skill install jeremylongshore/tons-of-skills-marketplace analyzing-database-indexes --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-database-indexes .claude/skills/analyzing-database-indexes && 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-database-indexes
GitHub stars
2.8k
Token cost
~2k tokens
SKILL.md length
874 words
Files
5 (incl. scripts, references, assets)
Skills in repo
3,342
Repo updated
First seen
Licence
MIT

At a glance

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

  • Works in 10 steps: Identify tables with high sequential… → Find the queries causing sequential… → Analyze query WHERE clauses and JOIN… → …
  • You need to work with database indexing
  • SKILL.md covers Overview, Prerequisites, Instructions and Output, plus 3 more sections
  • Runs Python scripts from its folder

What it does

Analyzing Database Indexes is an agent skill from jeremylongshore/tons-of-skills-marketplace. Process use when you need to work with database indexing. This skill provides index design and optimization with comprehensive guidance and automation. Trigger with phrases like "create indexes", "optimize indexes", or "improve query performance".

Its SKILL.md is about 2k tokens, which your agent loads only when the skill is triggered. The skill folder holds 7 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 and MySQL. 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 database indexing
  • With phrases like create indexes
  • Optimize indexes
  • Improve query performance

Example prompts

  • “create indexes”
  • “optimize indexes”
  • “improve query performance”
  • “/analyzing-database-indexes”

Requirements

  • Python 3
  • 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 tables with high sequential scan activity (candidates for missing indexes)
  2. Find the queries causing sequential scans by correlating with pg_stat_statements
  3. Analyze query WHERE clauses and JOIN conditions to determine which columns need indexes. Extract the filtering columns and their selectivity
  4. Recommend composite indexes for multi-column queries. Follow the equality-first, range-second ordering
  5. Identify unused indexes wasting write performance
  6. Detect redundant indexes where one index is a prefix of another
  7. Evaluate partial indexes for filtered queries. If a query always filters WHERE status = 'active'
  8. Consider covering indexes (INCLUDE clause in PostgreSQL 11+) for index-only scans
  9. Estimate the impact of each recommendation
  10. Generate a prioritized recommendations report with CREATE INDEX and DROP INDEX statements, estimated storage impact, expected query…

What it can do on your machine

Read from SKILL.md and the folder at commit cfae287. 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 2 files in scripts/ (Python), 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
    • github.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 Database Indexes loads about 2k tokens when it runs, and up to ~2k if it reads all its reference files. Until then it costs about 69 tokens; SKILL.md has 874 words of instructions outside code blocks.

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

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 cfae287, republished under its MIT licence (© jeremylongshore). 874 words, ~1,952 tokens.

Download SKILL.mdSave it as .claude/skills/analyzing-database-indexes/SKILL.md (or your agent's skills folder). This skill also uses 4 other files; get the full folder from GitHub.
name
analyzing-database-indexes
description
Process use when you need to work with database indexing. This skill provides index design and optimization with comprehensive guidance and automation. Trigger with phrases like "create indexes", "optimize indexes", or "improve query performance".
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-database

Database Index Advisor

Overview

Analyze database index usage, identify missing indexes causing sequential scans, detect redundant or unused indexes wasting write performance, and recommend optimal index configurations for PostgreSQL and MySQL.

Prerequisites

  • Database credentials with access to pg_stat_user_indexes, pg_stat_user_tables, and pg_stat_statements (PostgreSQL) or performance_schema and sys schema (MySQL)
  • pg_stat_statements extension enabled for PostgreSQL query statistics
  • psql or mysql CLI for executing analysis queries
  • Representative workload running (analysis during off-peak hours may miss important query patterns)
  • At least 24 hours of statistics accumulation since the last pg_stat_reset()

Instructions

  1. Identify tables with high sequential scan activity (candidates for missing indexes):

    • PostgreSQL: SELECT relname, seq_scan, seq_tup_read, idx_scan, n_live_tup FROM pg_stat_user_tables WHERE seq_scan > 100 AND n_live_tup > 10000 ORDER BY seq_tup_read DESC LIMIT 20
    • A table with high seq_scan count and high seq_tup_read relative to n_live_tup is scanning most of the table repeatedly

  2. Find the queries causing sequential scans by correlating with pg_stat_statements:

    • SELECT query, calls, mean_exec_time, rows FROM pg_stat_statements WHERE query ILIKE '%table_name%' ORDER BY mean_exec_time DESC LIMIT 10
    • Run EXPLAIN (ANALYZE, BUFFERS) on the top queries to confirm sequential scan usage

  3. Analyze query WHERE clauses and JOIN conditions to determine which columns need indexes. Extract the filtering columns and their selectivity:

    • SELECT column_name, n_distinct, correlation FROM pg_stats WHERE tablename = 'target_table'
    • High n_distinct (close to row count) indicates good index selectivity
    • correlation close to 1.0 or -1.0 suggests the column benefits from a B-tree index

  4. Recommend composite indexes for multi-column queries. Follow the equality-first, range-second ordering:

    • Place columns used with = operators first in the index
    • Place columns used with >, <, BETWEEN, or LIKE 'prefix%' last
    • Example: WHERE status = 'active' AND created_at > '2024-01-01' -> CREATE INDEX ON orders (status, created_at)

  5. Identify unused indexes wasting write performance:

    • PostgreSQL: SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey' ORDER BY pg_relation_size(indexrelid) DESC
    • Indexes with zero scans over a representative period are candidates for removal (verify they are not used by foreign key constraints or unique enforcement)

  6. Detect redundant indexes where one index is a prefix of another:

    • A single-column index on (customer_id) is redundant if a composite index on (customer_id, created_at) exists, because the composite index serves both single-column and multi-column queries
    • Generate DROP INDEX recommendations for the redundant subset indexes

  7. Evaluate partial indexes for filtered queries. If a query always filters WHERE status = 'active':

    • CREATE INDEX idx_orders_active ON orders (created_at) WHERE status = 'active'
    • Partial indexes are smaller and faster than full indexes when the filter eliminates most rows

  8. Consider covering indexes (INCLUDE clause in PostgreSQL 11+) for index-only scans:

    • CREATE INDEX idx_orders_covering ON orders (customer_id, created_at) INCLUDE (total_amount, status)
    • The INCLUDE columns are stored in the index leaf pages, enabling index-only scans without heap access

  9. Estimate the impact of each recommendation:

    • Index size: SELECT pg_size_pretty(pg_relation_size('index_name')) for existing similar indexes
    • Write overhead: each additional index adds approximately 5-15% write latency per INSERT/UPDATE
    • Read improvement: compare EXPLAIN plans with and without the proposed index

  10. Generate a prioritized recommendations report with CREATE INDEX and DROP INDEX statements, estimated storage impact, expected query improvement, and write overhead trade-off analysis.

Show full SKILL.md (360 more words)Show less

Output

  • Missing index recommendations as ready-to-execute CREATE INDEX statements with CONCURRENTLY option
  • Unused index report with DROP INDEX candidates and their storage savings
  • Redundant index report identifying prefix-overlapping indexes
  • Index usage statistics showing scan counts, tuple reads, and sizes for all indexes
  • Impact analysis estimating read improvement vs. write overhead for each recommendation

Error Handling

ErrorCauseSolution
pg_stat_statements not availableExtension not installedCREATE EXTENSION pg_stat_statements and add to shared_preload_libraries
Index creation blocks writesCREATE INDEX acquires exclusive lock on the tableUse CREATE INDEX CONCURRENTLY which does not block writes (takes longer but safe for production)
Index not used after creationStatistics not updated or query planner choosing sequential scanRun ANALYZE table_name; check random_page_cost setting (reduce to 1.1 for SSD); verify query uses indexed columns without functions
Statistics reset unexpectedlypg_stat_reset() called or database restart cleared statsWait 24-48 hours for statistics to accumulate; set up periodic stats collection to a metrics table
Too many indexes on write-heavy tableEach INSERT/UPDATE must update all indexesTarget 5-7 indexes per table maximum; use composite indexes to replace multiple single-column indexes; remove unused indexes

Examples

Identifying a missing composite index for an API endpoint: The /orders?customer_id=123&status=active endpoint takes 2 seconds. Analysis shows the orders table (5M rows) has indexes on (id) and (customer_id) but not (customer_id, status). The query filters on both columns. Adding CREATE INDEX CONCURRENTLY idx_orders_customer_status ON orders (customer_id, status) reduces the query to 5ms.

Cleaning up 8 unused indexes saving 12GB: Index usage analysis reveals 8 indexes with zero scans over 30 days, totaling 12GB of storage. After confirming none are used for FK enforcement or unique constraints, dropping them reduces write latency by 18% and frees disk space. Command: DROP INDEX CONCURRENTLY idx_name.

Replacing 3 single-column indexes with 1 composite covering index: Table has separate indexes on (user_id), (created_at), and (status). Most queries filter on all three. A single composite index (user_id, status, created_at) INCLUDE (amount) replaces all three, reduces total index storage by 40%, and enables index-only scans for the dashboard query.

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 4 other files (scripts, references, assets) in skills/.curated/analyzing-database-indexes of jeremylongshore/tons-of-skills-marketplace.

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

Open the folder on GitHubat commit cfae287

Compare with similar skills

Analyzing Database Indexes 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 Database Indexes compared with similar skills
SkillStarsUsed inTokensAuto-checkLicenceRepo updated
Analyzing Database Indexes this skilljeremylongshore/tons-of-skills-marketplace2.8k—~2kAutomated safety check: PassMIT
DB Ops SopOpenDCAI/DataMind451—~388Automated safety check: PassApache-2.0
Altimate Data Warehouse DelegateAltimateAI/data-engineering-skills128—~1.4kAutomated safety check: PassMIT
Database OptimizerJeffallan/claude-skills12k—~1.6kAutomated safety check: PassMIT
SQL ProJeffallan/claude-skills12k—~1.3kAutomated safety check: PassMIT
Database OptimizerAratKruglik/claude-laravel155—~1kAutomated safety check: PassNone

Similar skills

  • DB Ops Sop

    OpenDCAI/DataMind

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

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

  • Altimate Data Warehouse Delegate

    AltimateAI/data-engineering-skills

    Delegates dbt and warehouse tasks such as lineage, migrations and cost attribution to the altimate-code CLI agent and relays its answer back.

    128 GitHub stars~1.4k tokensUpdated 2 days ago
    DatabasesAuto-check passed

  • Database Optimizer

    Jeffallan/claude-skills

    Tunes PostgreSQL and MySQL performance by analyzing slow queries and execution plans, designing indexes, rewriting queries and adjusting configuration, one validated change at a time.

    12k GitHub stars~1.6k tokensUpdated 7 days ago
    DatabasesAuto-check passed

  • SQL Pro

    Jeffallan/claude-skills

    Optimizes SQL queries and designs schemas using CTEs, window functions, covering indexes and EXPLAIN ANALYZE, with notes on dialect differences between major databases.

    12k GitHub stars~1.3k tokensUpdated 7 days ago
    DatabasesAuto-check passed

  • Database Optimizer

    AratKruglik/claude-laravel

    A skill your agent uses when investigating slow queries, analyzing execution plans, or optimizing database performance.

    155 GitHub stars~1k tokensUpdated 5 mo ago
    DatabasesAuto-check passed

  • Database Optimizer

    zebbern/claude-code-guide

    A skill your agent uses when investigating slow queries, analyzing execution plans, or optimizing database performance.

    4.7k GitHub stars~967 tokensUpdated today
    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 today
    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 today
    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 today
    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 today
    Auto-check passed

  • Analyzing Dependencies

    jeremylongshore/tons-of-skills-marketplace

    Analyze dependencies for known security vulnerabilities and outdated versions.

    2.8k GitHub stars~1.7k tokensUpdated today
    Auto-check passed

Works with

Categories

Questions about Analyzing Database Indexes

What does Analyzing Database Indexes do?

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

When should I use Analyzing Database Indexes?

Analyzing Database Indexes fits situations like: you need to work with database indexing; with phrases like create indexes; optimize indexes; improve query performance.

How do I install Analyzing Database Indexes in Claude Code?

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

How do I install Analyzing Database Indexes in Codex?

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

Can I use Analyzing Database Indexes 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-database-indexes -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-database-indexes, .gemini/skills/analyzing-database-indexes, .github/skills/analyzing-database-indexes and .opencode/skills/analyzing-database-indexes in your project.

What does Analyzing Database Indexes need to run?

Going by SKILL.md and its folder, Analyzing Database Indexes needs Python for the scripts in its folder. Our summary lists: Python 3. 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 Database Indexes access the network?

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

Is Analyzing Database Indexes 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 Database Indexes use?

Analyzing Database Indexes 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 Database Indexes use?

About 2k tokens (SKILL.md is roughly 7.8k 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 17 tokens, read only when the agent opens those files.

What are the alternatives to Analyzing Database Indexes?

Skills that share tags, products or a category with Analyzing Database Indexes: DB Ops Sop (OpenDCAI/DataMind, 451 stars), Altimate Data Warehouse Delegate (AltimateAI/data-engineering-skills, 128 stars), Database Optimizer (Jeffallan/claude-skills, 12k stars) and SQL Pro (Jeffallan/claude-skills, 12k stars). The comparison table on this page puts their stars, adoption, token cost, safety result and licence side by side.

Who maintains Analyzing Database Indexes?

jeremylongshore (a GitHub user) maintains it in jeremylongshore/tons-of-skills-marketplace, which has 2,827 GitHub stars. The repository holds 3,342 skills in this directory. The repository was last updated on October 10, 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.