- Home
- Skills
- Data & Databases
- PostgreSQL Slow Query Finder and EXPLAIN Plan Fixer
Works with the AI Agents you already use
PostgreSQL Slow Query Finder and EXPLAIN Plan Fixer
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
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)
| # | queryid | calls | mean ms | total % | Verdict |
|---|---|---|---|---|---|
| 1 | 4471… | 9,120 | 1,840 | 61.3 | NEEDS INDEX |
| 2 | 8812… | 412,300 | 0.9 | 13.5 | FAST |
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):
| metric | before | after index | after keyset |
|---|---|---|---|
| execution time | 1,842 ms | 38.6 ms | 0.41 ms |
| buffers (hit + read) | 40,113 | 10,240 | 9 |
| key node | Seq Scan + disk Sort | Index Scan, 10,050 rows | Index 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.
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
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
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 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 29 days ago
Requires