Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIMuseMuseDotsDots+21 more

    sql optimization patterns

    1

    Optimize SQL performance through EXPLAIN plan analysis, index design, and query refactoring.

    $8

    Secure checkout via Stripe

    Money-back guarantee

    Converts to your local currency at checkout

    Security scanned
    sql optimization patterns

    sql optimization patterns

    Example session with this skill installed

    I have a slow query on PostgreSQL. Table 'orders' has 2M rows. Query: SELECT * FROM orders WHERE status = 'pending' AND created_at > '2023-01-01' ORDER BY created_at DESC. Here is the EXPLAIN plan.

    • Read your context and instructions
    • Compiled the sql optimization patterns

    The analysis indicates a Parallel Seq Scan on the orders table because the current index on created_at is not being utilized efficiently alongside the status filter. The query cost is high due to the filter operation occurring after the disk read.

    SQL Optimization Report

    ComponentFindingRecommendation
    Current PlanParallel Seq Scan on orders (cost=0.00..45823.10)The planner is ignoring the single-column index on created_at due to the status filter.
    BottleneckFilter: (status = 'pending'::text)85% of execution time is spent filtering rows in memory after a full table scan.
    Index StrategyMissing Composite IndexCreate a composite index covering both the filter and sort columns to allow an Index Scan.
    Query RewriteSelectivity IssuesReplace SELECT * with specific columns to reduce memory overhead and potentially allow an Index Only Scan.

    Proposed Index

    CREATE INDEX idx_orders_status_created_at_desc 
    ON orders (status, created_at DESC);
    

    Performance Validation Plan

    1. Baseline: Run EXPLAIN ANALYZE on the current query three times to establish a median execution time.
    2. Apply Change: Execute the CREATE INDEX statement (preferably CONCURRENTLY in production).

    Verify: Run EXPLAIN ANALYZE again. Expected result is an Index Scan or Index Only Scan using idx_orders_status_created_at_desc.
    4. Rollback: DROP INDEX idx_orders_status_created_at_desc; if execution time does not decrease by at least 40%.

    Next steps

    • Provide the output of \d orders to confirm there are no conflicting indexes.
    • Run the baseline EXPLAIN ANALYZE and share the total execution time in milliseconds.
    • Confirm if this table receives high write volume, as additional indexes impact INSERT performance.

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

    About this skill

    The problem

    Slow SQL queries drag down application performance and increase cloud costs. Identifying the exact cause in an EXPLAIN plan or designing the correct composite index is time-consuming and prone to error.

    What it does

    • Analyzes EXPLAIN and EXPLAIN ANALYZE output to identify bottleneck operations like sequential scans or costly joins.
    • Audits existing indexing strategies and proposes specific composite, partial, or functional indexes based on query predicates.
    • Detects N+1 query patterns in application code and suggests batching or join-based rewrites.
    • Provides structured optimization reports including a baseline measurement, proposed changes, and a validation plan.

    Frameworks & tools

    PostgreSQL, MySQL, MariaDB, and SQL Server. Compatible with ORMs like Eloquent, TypeORM, and SQLAlchemy for identifying N+1 issues.

    Why this beats prompting it yourself

    Generic LLM prompts often suggest "add an index" without checking existing constraints or table cardinality. This skill enforces a strict evidence-based workflow, requiring plan analysis before making recommendations to avoid redundant indexes and production regression.

    Use cases

    • Triaging slow queries reported in APM tools like New Relic or Datadog.
    • Reviewing schema migrations to ensure new features have appropriate indexing from day one.
    • Refactoring legacy SQL queries that have slowed down as table volume reached millions of rows.

    Known limitations

    Requires table schema or cardinality information to provide accurate cost estimates. Cannot execute changes directly on production databases.

    Requires EXPLAIN plan output for accurate analysis. Cannot perform destructive actions or schema changes without manual review and approval.

    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

    Money-back guarantee

    Doesn't do what its page promises? You get your money back.

    Trust & safety

    Security scan

    Security scan passed 2 months ago

    Permissions

    No special permissions declared or detected

    Listed2 months ago

    What's inside

    Frequently Asked Questions

    This skill analyzes EXPLAIN plans and schema structures to provide evidence-based refactoring suggestions, such as designing specific composite indexes and resolving N+1 query patterns, rather than just offering generic SQL advice.

    The skill is optimized for major relational databases including PostgreSQL, MySQL, MariaDB, and SQL Server, and it can also analyze patterns within popular ORMs like Eloquent or SQLAlchemy.

    You will receive a modular set of prompts and workflows designed to be imported into your preferred AI agent or LLM interface to guide it through professional-grade performance tuning.

    For the most accurate results, you should provide the query text, the output of an EXPLAIN ANALYZE command, and basic schema details like table row counts and existing index structures.

    No, for safety reasons, the skill provides a structured validation plan and the necessary SQL commands for you to review and execute manually in your development or staging environment.

    Buyers receive lifetime access to the current version of the SQL optimization patterns, including any minor updates or refinements made to the logic to account for new database engine features.

    New in Data & Databases