postgres query optimizer

    1

    Optimizes slow Postgres queries using EXPLAIN plan analysis, providing ranked rewrites and safe index DDL.

    $9

    Secure checkout via Stripe

    Money-back guarantee

    Converts to your local currency at checkout

    Security scanned

    Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIDeepSeekDeepSeekMuseMuse+22 more

    About this skill

    Slow Postgres queries often stall production without a clear path to resolution. This skill analyzes SQL queries and EXPLAIN plan output to identify the specific causes of latency, from sequential scans to nested loop blowups. It provides a prioritized, falsifiable rewrite plan rather than generic tuning advice.

    What it does

    • Query diagnosis identifies the dominant cause of slowness by analyzing self-time in EXPLAIN plans.
    • Rewrite generation provides semantically equivalent SQL optimized for the Postgres planner.
    • Index DDL generates safe CREATE INDEX CONCURRENTLY and rollback statements.
    • Impact estimation predicts win ratios and page read reductions based on plan data.
    • Validation checklists deliver step-by-step instructions to verify performance gains in production.

    How it works

    1. Input collection of the SQL query, EXPLAIN (ANALYZE, BUFFERS) output, and relevant schema.
    2. Plan parsing via a Python-based analyzer to attribute time to specific nodes and detect signals like sort spills or row misestimates.
    3. Heuristic mapping against a library of Postgres-specific performance rules (H-01 to H-38).
    4. Report generation providing a verdict, ranked fixes, rewritten SQL, and a validation plan.

    Frameworks & tools

    This skill is purpose-built for PostgreSQL versions 12 through 18. It uses a specialized Python parser for large plan analysis and adheres to specific Postgres index patterns and planner heuristics.

    Why this beats prompting it yourself

    Generic LLMs often struggle with the loop arithmetic required to interpret EXPLAIN ANALYZE output correctly, frequently confusing per-loop averages with total time. This skill enforces a rigorous diagnostic procedure, cites specific heuristic IDs, and includes mandatory checks for semantic changes that could break application logic.

    Use cases

    • Troubleshooting a query that suddenly slowed down after table growth.
    • Determining why the planner is ignoring a specific index in favor of a sequential scan.
    • Optimizing complex CTEs or subqueries that are causing hash join spills to disk.
    • Evaluating the write-cost and redundancy of new indexes before deployment.

    Known limitations

    Does not support non-Postgres databases. Analysis is restricted to structural checks if no EXPLAIN plan is provided. It does not audit migration locking risks or server-wide GUC tuning.

    Find more skills like this on the Claude Skills Marketplace, the Codex Skills Marketplace and the Gemini Skills Marketplace, or browse Claude Skills for APIs & Backend.

    Supports Postgres 12-18 only. Analysis is structural-only without EXPLAIN plans. Above 8KB, plans are summarized before reasoning. Does not audit migration locking risks.

    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 buying a skill.

    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 9 days ago

    Permissions

    Terminal / Shell
    Read Files
    Write Files
    Network Access
    Listed9 days ago

    What's inside

    Frequently Asked Questions

    You need to provide the SQL query, the `EXPLAIN (ANALYZE, BUFFERS)` output, and your table schemas. The `BUFFERS` flag is critical as it allows the skill to analyze cache hits and disk I/O, providing a more accurate diagnosis of performance bottlenecks.

    Unlike generic AI, this skill uses a specialized Python-based parser to accurately calculate total execution time from per-loop averages in EXPLAIN plans. It also maps issues to a library of 38 specific Postgres performance heuristics, ensuring suggestions are based on database science rather than generic guesses.

    This skill is purpose-built for PostgreSQL versions 12 through 18. While it can suggest structural SQL improvements for other SQL dialects, the deep EXPLAIN plan analysis and index DDL generation are specifically calibrated for the Postgres planner.

    Yes, the skill generates `CREATE INDEX CONCURRENTLY` statements to prevent table locking in production. It also includes rollback DDL and a validation checklist to ensure performance gains are verified before you finalize any changes.

    The skill is designed to handle complex queries involving Common Table Expressions (CTEs), subqueries, and heavy joins. It specifically identifies issues like hash join spills to disk and row-estimate mismatches that often cause these complex structures to underperform.

    New in APIs & Backend