Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIMuseMuseDotsDots+21 more

    sql performance surgeon

    1

    Diagnose and fix slow SQL queries using execution plan analysis, index optimization, and query refactoring.

    Free

    Security scanned
    sql performance surgeon

    sql performance surgeon

    Example session with this skill installed

    This Postgres query is taking 4 seconds: SELECT * FROM orders WHERE status = 'pending' AND created_at > '2024-01-01' ORDER BY created_at DESC; EXPLAIN ANALYZE shows a Seq Scan on 2M rows.

    • Read your context and instructions
    • Compiled the sql performance surgeon

    I have analyzed the execution plan. The engine is performing a full sequential scan because the current index on created_at cannot be used efficiently with the status filter, and SELECT * is forcing heap fetches for 2 million rows.

    SQL Performance Diagnosis

    ComponentObservationImpact
    OperatorSeq Scan on ordersReads 2M rows from disk instead of using an index.
    Predicatestatus = 'pending'Low selectivity but filters out 80% of rows late in the process.
    ProjectionSELECT *Increases IO overhead; prevents an Index-Only Scan.
    SortParallel B-tree SortHigh memory usage for the ORDER BY clause on a large result set.

    Rewritten Query

    -- Select only required columns to enable covering index usage
    SELECT id, order_number, total_amount, created_at 
    FROM orders 
    WHERE status = 'pending' 
      AND created_at > '2024-01-01' 
    ORDER BY created_at DESC;
    

    Index DDL

    -- Recommendation: Composite index with equality column first
    -- Write Cost: Moderate (1 additional index to maintain)
    CREATE INDEX CONCURRENTLY idx_orders_status_created 
    ON orders (status, created_at DESC);
    

    Next steps

    1. Run ANALYZE orders; to ensure the query planner has updated statistics before testing.
    2. Verify the new plan shows Index Scan or Index Only Scan instead of Seq Scan.
    3. Audit for existing single-column indexes on status or created_at that this new composite index might render redundant.

    Connects securely to your tools. The creator never sees your data.

    About this skill

    Stop guessing why your database is slow. Most SQL performance issues stem from poor execution plans, sargability traps, or inefficient index design that taxes your write throughput. This skill applies a systematic diagnostic method to move past "add an index" and solve the root cause.

    What it does

    • Analyze execution plans by identifying smoking-gun operators like seq scans, disk spills, and row estimate mismatches.
    • Detect sargability traps such as implicit type casts, leading wildcards, and function-wrapped columns that disable indexes.
    • Design composite indexes following the equality-first rule while accounting for write-cost tradeoffs and maintenance overhead.
    • Refactor query shapes to eliminate N+1 patterns, replace costly DISTINCT calls, and implement keyset pagination.
    • Identify schema bottlenecks where partitioning, denormalization, or summary tables are required instead of just query tuning.

    How it works

    1. Submit your SQL and plan using engine-specific commands like EXPLAIN (ANALYZE, BUFFERS) for Postgres or EXPLAIN ANALYZE for MySQL.
    2. Receive a diagnosis that narrates the execution plan and identifies specific bottlenecks.
    3. Apply the rewritten SQL and DDL provided, including safety notes for production index builds.

    Frameworks & tools

    Supports Postgres, MySQL 8+, SQL Server, and SQLite. Works with raw SQL or output generated by ORMs like Prisma, TypeORM, and SQLAlchemy.

    Why this beats prompting it yourself

    Generic AI often suggests indexes without considering write costs or specific engine behaviors like parameter sniffing. This skill enforces a strict diagnostic order, ensuring you don't build redundant indexes or ignore stale statistics that make even "perfect" queries slow.

    Use cases

    • Debugging a query that runs fast in development but times out in production.
    • Refactoring ORM-generated queries that are causing N+1 performance hits.
    • Designing covering indexes for high-traffic read paths to achieve index-only scans.
    • Analyzing why a previously fast query is degrading as table volume grows.

    Known limitations

    Does not perform live database connections; requires manual input of query plans. Focuses on query and index logic rather than server-level hardware configuration or kernel tuning.

    Requires manual input of EXPLAIN plans. Does not handle hardware-level tuning or OS kernel parameters. Engine-specific syntax is limited to Postgres, MySQL, SQL Server, and SQLite.

    How to use

    1. 1

      Connect your agent to the Agensi MCP

      Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup

    2. 2

      Sign in to Agensi

      Your agent shows a sign-in link. Sign in and click Allow.

    3. 3

      Ask for what you need

      Your agent loads this skill and uses it on your task. It always asks before any payment.

    Reviews

    No reviews yet

    Be one of the first to try it. Every listed skill goes through the trust checks below.

    Security scanned

    Checked by our 8-point scan before listing

    Fresh listing

    Recently published to Agensi

    Free forever

    No account required to browse

    Trust & safety

    Security scan

    Security scan passed 22 days ago

    • Free with an Agensi account

    Permissions

    No special permissions declared or detected

    Listed22 days ago

    What's inside

    Frequently Asked Questions

    The skill requires the raw SQL query and its execution plan (output from commands like EXPLAIN ANALYZE). This data provides the necessary context on how your specific database engine is processing the data without requiring a direct connection to your production environment.

    Yes, the skill is highly effective at refactoring inefficient SQL generated by ORMs like Prisma, Sequelize, or SQLAlchemy. It identifies common ORM pitfalls like N+1 queries and bloated SELECT statements, providing optimized raw SQL or architectural advice to fix them.

    Unlike generic LLMs, this skill follows a strict diagnostic framework that prioritizes "sargability" and accounts for write-heavy performance trade-offs. It avoids recommending redundant indexes and focuses on the root cause, such as stale statistics or disk spills, rather than just suggesting surface-level fixes.

    The skill supports major relational engines including PostgreSQL, MySQL (8.0+), SQL Server, and SQLite. While it focuses on these core engines, its logic for index design and query refactoring is applicable to most SQL-based relational databases.

    No, for security and privacy reasons, the skill operates entirely on the data you paste into the chat. It does not require or support direct access to your database instances or sensitive credentials.

    You receive full access to the SQL Performance Surgeon skill, which includes the diagnostic logic for execution plan analysis, index design recommendations, and query refactoring strategies for all supported database engines.

    New in Data & Databases