- Home
- Skills
- Data & Databases
- PostgreSQL Execution Plan Analyzer
Works with the AI Agents you already use
PostgreSQL Execution Plan Analyzer
Analyze PostgreSQL EXPLAIN plans to fix Seq Scans, disk spilling, and buffer cache bottlenecks.
$15
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
| Metric | Value | Status |
|---|---|---|
| Main Bottleneck | External Merge Sort (Disk) | 🔴 CRITICAL |
| Access Method | Sequential Scan | 🟡 WARNING |
| Disk Usage | 4200kB | 🔴 HIGH |
| Buffer Cache Hit Ratio | 70% | 🟡 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_atindex to the production-like environment. - Re-run the
EXPLAIN (ANALYZE, BUFFERS)command to verify theSort Methodchanged toquicksortin memory. - Monitor
shared hitblocks 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_memadjustments.
How it works
- Supply the raw text output of your PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)command. - The agent audits the node costs and identifies the operations consuming the most runtime.
- It checks buffer usage to determine if performance is degraded by disk reads.
- 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.
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.
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
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 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 3 days ago
- Software
- Zero global installationorelevated privileges required.
File System Read/Write (strictly scoped to current project workspace `./`).
Permissions
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.