Works with the AI Agents you already use

    Claude CodeClaude CodeCursorCursorCodex CLICodex CLIDeepSeekDeepSeekMuseMuse+22 more

    PostgreSQL Slow Query Finder and EXPLAIN Plan Fixer

    1

    Find costly Postgres queries via pg_stat_statements, read EXPLAIN ANALYZE safely, fix with an index, rewrite or stats, prove it before/after. Graded FAST, NEEDS INDEX or REWRITE.

    $5

    Secure checkout via Stripe

    Money-back guarantee

    Converts to your local currency at checkout

    Security scanned
    PostgreSQL Slow Query Finder and EXPLAIN Plan Fixer

    PostgreSQL Slow Query Finder and EXPLAIN Plan Fixer

    Example session with this skill installed

    Our Django order list endpoint takes about 2 seconds on deep pages. We run PostgreSQL 18 on RDS with a read replica and pg_stat_statements enabled. Find what is slow and fix it.

    • Read your context and instructions
    • Compiled the postgresql slow query

    Slow query report: shop (PostgreSQL 18.6, stats since 2026-10-12)

    Ran on: read replica · ANALYZE used: yes (SELECT only)

    #queryidcallsmean mstotal %Verdict
    14471…9,1201,84061.3NEEDS INDEX
    28812…412,3000.913.5FAST

    Q1 order list, page N: NEEDS INDEX

    Source: orders/views.py:58 (Order.objects.filter(tenant=t, status="open").order_by("-created_at")[offset:offset+50])

    Root cause: no index on (tenant_id, status, created_at). Postgres reads every row for the tenant, then sorts on disk. Proven by:

    Seq Scan on orders (actual rows=48210.00 loops=1)
      Filter: ((tenant_id = 42) AND (status = 'open'))
      Rows Removed by Filter: 1951790
    Sort Method: external merge  Disk: 9384kB
    

    Fix 1 (index, separate migration with atomic = False):

    CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
      ON orders (tenant_id, status, created_at DESC, id DESC);
    

    Fix 2 (rewrite): OFFSET 10000 still walks 10,050 index entries. Switch to keyset pagination:

    qs.filter(Q(created_at__lt=c) | Q(created_at=c, id__lt=i))
      .order_by("-created_at", "-id")[:50]
    

    Before / after (tenant 42, page 201, warm cache):

    metricbeforeafter indexafter keyset
    execution time1,842 ms38.6 ms0.41 ms
    buffers (hit + read)40,11310,2409
    key nodeSeq Scan + disk SortIndex Scan, 10,050 rowsIndex Scan, 50 rows

    Cost: index estimated at about 60 MB on 2.0M rows (check pg_relation_size after the build). orders gets about 40 inserts per minute, so the write cost is low.

    Q2 order by id: FAST

    Index Scan on orders_pkey, 1 row, 0.9 ms mean. High call count from the detail page, which is expected.

    Index hygiene

    idx_orders_tenant (tenant_id) is a prefix of the new index and has 0 scans on the replica. Candidate to drop after you check the primary. Do not drop without approval.

    Not verified

    Primary index usage counters not checked (replica only).

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

    About this skill

    Slow PostgreSQL queries are often fixed by guesswork. Someone adds an index based on the query text, nobody measures the result, and the table ends up with three overlapping indexes that slow down every write. Running EXPLAIN ANALYZE on the wrong statement in production can also change data, because ANALYZE executes the query.

    This skill works from evidence and keeps the analysis safe.

    What it does:

    • Finds the queries that cost the most using pg_stat_statements, with column names for current versions (total_exec_time, mean_exec_time, stats_since). Falls back to auto_explain, slow query logs or your APM
    • Traces each query to the Prisma, Django, Rails or SQLAlchemy code that generates it, and flags N+1 patterns
    • Runs EXPLAIN (ANALYZE, BUFFERS) only where it is safe: on a replica or staging if possible, with a statement timeout, writes rolled back, and plain EXPLAIN for writes on a production primary
    • Reads the plan for seq scans, row-estimate mismatches, per-loop nested loops, sorts and hashes spilling to disk, filter vs index conditions, and heap fetches, and quotes the line that proves the root cause
    • Applies the smallest fix: a query rewrite, the right index (composite, partial, INCLUDE or expression) built with CREATE INDEX CONCURRENTLY, or statistics. Per-session config changes come last
    • Measures before and after with the same parameters, and checks write cost, unused indexes and duplicate indexes

    Each query gets a verdict of FAST, NEEDS INDEX or REWRITE, with a report template.

    Who it's for: backend developers, SREs and data engineers who run PostgreSQL and want fixes they can defend in a code review.

    Honesty note: every timing and row count must come from output you ran or pasted. Estimates are labeled. The reference was checked against the postgresql.org docs for PostgreSQL 18 in October 2026.

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

    PostgreSQL only (written against the PostgreSQL 18 docs, with notes for older column names). It needs a replica, a staging copy or pasted plans to give measured results, and it will not run EXPLAIN ANALYZE on writes against a production primary. It does not cover server-wide config tuning, lock contention, replication or hardware sizing.

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

    Software
    psql
    oranother SQL client
    Database read access
    Optional
    pg_stat_statements

    A replica or staging copy is preferred. pg_stat_statements is an extension you enable.

    Permissions

    Write Files
    Listed29 days ago

    What's inside

    Frequently Asked Questions

    SKILL.md with the workflow, safety rules, verdict and output template, plus references/postgres-query-tuning.md with pg_stat_statements column names by version, auto_explain settings, EXPLAIN red flags, index and rewrite patterns, statistics commands, index-health queries and ORM tracing tips. Sources are postgresql.org docs, checked October 2026.

    Any agent that reads the SKILL.md format, such as Claude Code, Codex CLI, Cursor, VS Code Copilot and Gemini CLI. It works best when the agent can run psql or a database client and read your repo.

    Unzip the folder into your agent's skills directory. For live analysis, expose a connection string to a replica or staging database through an environment variable. Without a database it can still read EXPLAIN output you paste, but it marks conclusions as untested.

    PostgreSQL only. It does not tune server-wide configuration, diagnose lock contention or replication, or replace load testing. Results depend on running plans against production-like data. It will not report speedups it did not measure.

    Plain prompts tend to suggest indexes from the query text and guess at gains. This skill requires a plan line as proof for each root cause, runs EXPLAIN ANALYZE only in safe places (writes rolled back, replica preferred), uses CREATE INDEX CONCURRENTLY for production, measures before and after with the same parameters, and checks write cost and duplicate indexes.

    No. It focuses on query-level fixes: indexes, query rewrites and planner statistics, with a session-level work_mem check as a last resort. Connection pooling, hardware sizing and disk IOPS are out of scope. It notes column-name differences for older PostgreSQL versions; the facts file is written against PostgreSQL 18.

    New in Data & Databases