Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIMuseMuseDotsDots+21 more

    PostgreSQL Execution Plan Analyzer

    1

    Analyze PostgreSQL EXPLAIN plans to fix Seq Scans, disk spilling, and buffer cache bottlenecks.

    $15

    Secure checkout via Stripe

    Money-back guarantee

    Converts to your local currency at checkout

    Security scanned
    PostgreSQL Execution Plan Analyzer

    PostgreSQL Execution Plan Analyzer

    Example session with this skill installed

    Analyze this plan
    Sort (cost=125.33..128.52 rows=1276 width=41) (actual time=15.2..15.4 rows=1276 loops=1)
    Sort Key: created_at
    Sort Method: external merge Disk: 4200kB
    -> Seq Scan on orders (cost=0.00..60.76 rows=1276 width=41) (actual time=0.01..1.2 rows=1276 loops=1)
    Buffers: shared hit=42 read=18

    • Read your context and instructions
    • Compiled the postgresql execution plan

    I have analyzed the provided execution plan. The primary bottleneck is an external disk sort and a sequential scan on the orders table.

    PostgreSQL Optimization Plan

    MetricValueStatus
    Main BottleneckExternal Merge Sort (Disk)🔴 CRITICAL
    Access MethodSequential Scan🟡 WARNING
    Disk Usage4200kB🔴 HIGH
    Buffer Cache Hit Ratio70%🟡 SUBOPTIMAL

    Analysis & Recommendations

    Memory Spilling: The sort operation is using external merge on disk (4200kB). This indicates that the current work_mem setting is too low for this query. Increasing work_mem to at least 8MB for this session will move the sort into RAM and significantly reduce latency.
    2.

    Sequential Scan: The engine is scanning the entire orders table. While the row count is currently low (1276), this will not scale.
    3.

    I/O Pressure: A 70% hit ratio indicates that 30% of the data pages are being fetched from disk rather than the shared buffer pool.

    Optimized SQL & Schema

    -- 1. Create a B-Tree index to allow Index Scan and pre-sorted data
    CREATE INDEX idx_orders_created_at ON orders (created_at);
    
    -- 2. Suggested session-level tuning for high-memory sorts
    SET work_mem = '8MB';
    
    -- 3. Optimized Query (Ensure index usage)
    SELECT * FROM orders 
    ORDER BY created_at;
    

    Next steps

    • Apply the idx_orders_created_at index to the production-like environment.
    • Re-run the EXPLAIN (ANALYZE, BUFFERS) command to verify the Sort Method changed to quicksort in memory.
    • Monitor shared hit blocks to ensure they now cover 95%+ of the request.

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

    About this skill

    Query performance issues often hide behind complex execution trees that are difficult to parse manually. This skill acts as an elite DBA to dissect your PostgreSQL EXPLAIN output and pinpoint exactly why a query is stalling.

    What it does

    • Plan parsing processes EXPLAIN (ANALYZE, BUFFERS) text to map the execution tree.
    • Bottleneck detection identifies expensive Sequential Scans, Nested Loop explosions, and heavy Disk Sorts.
    • Buffer audit calculates cache hit ratios using Shared Hit vs Shared Read blocks to find I/O pressure.
    • Optimization delivery provides rewritten SQL, specific index recommendations, or configuration tweaks like work_mem adjustments.

    How it works

    1. Supply the raw text output of your PostgreSQL EXPLAIN (ANALYZE, BUFFERS) command.
    2. The agent audits the node costs and identifies the operations consuming the most runtime.
    3. It checks buffer usage to determine if performance is degraded by disk reads.
    4. You receive a structured optimization plan including necessary schema or config changes.

    Frameworks & tools

    This skill is designed for PostgreSQL databases. It works with standard text-based execution plans and requires no direct database connection or credentials.

    Why this beats prompting it yourself

    Generic LLM prompts often miss subtle buffer cache issues or suggest generic indexes that do not solve the actual root cause. This skill uses a deterministic workflow focused on PostgreSQL internal metrics like shared hits and memory spilling to disk.

    Use cases

    • Eliminate Sequential Scans on large tables by identifying missing or ineffective indexes.
    • Resolve Hash Join and Sort operations that are spilling to disk by tuning memory parameters.
    • Debug high I/O wait times by analyzing buffer cache hit ratios in the execution plan.
    • Refactor complex CTEs or Subqueries that cause Nested Loop explosions.

    Known limitations

    Requires the ANALYZE and BUFFERS flags for full diagnostic depth. It does not provide automated execution or direct database migration capabilities.

    Requires EXPLAIN output to include ANALYZE and BUFFERS flags for accurate I/O analysis. Does not perform direct database writes or automated migrations.

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

    Requires

    File System Read/Write (strictly scoped to current project workspace `./`).
    Zero global installation or elevated privileges required.

    Permissions

    Read Files

    Target Use Cases: - Analyze this PostgreSQL EXPLAIN ANALYZE output. - Optimize a slow PostgreSQL query using its execution plan. - Eliminate sequential scan and disk sort in PostgreSQL query.

    Listed3 days ago

    What's inside

    Frequently Asked Questions

    Simply run your query using 'EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)' and paste the resulting text output into the agent. The tool uses these specific flags to analyze real-time execution costs and buffer cache performance.

    This skill is a specialized logic module that can be integrated into any AI agent framework that supports text analysis, such as AutoGPT, LangChain, or custom LLM-based assistants. It acts as a plug-and-play DBA layer for your existing AI workspace.

    Unlike general prompts, this skill uses a structured diagnostic workflow that specifically calculates I/O pressure and memory spills (work_mem) rather than just suggesting indexes. It focuses on PostgreSQL-specific metrics like Shared Hit vs Shared Read blocks to identify the true root cause of slowness.

    No, this skill operates entirely on the text output you provide and does not require direct access, credentials, or a connection to your database. This ensures your data remains secure while still receiving expert-level optimization advice.

    You receive the full skill definition, including the logic for parsing execution trees, identifying bottlenecks like Seq Scans, and generating specific SQL or configuration recommendations. Future updates to the analysis logic are included as PostgreSQL evolves.

    New in Data & Databases