- Home
- Skills
- APIs & Backend
- postgres query optimizer
postgres query optimizer
Optimizes slow Postgres queries using EXPLAIN plan analysis, providing ranked rewrites and safe index DDL.
$9
Works with the AI Agents you already use
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 CONCURRENTLYand 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
- Input collection of the SQL query,
EXPLAIN (ANALYZE, BUFFERS)output, and relevant schema. - Plan parsing via a Python-based analyzer to attribute time to specific nodes and detect signals like sort spills or row misestimates.
- Heuristic mapping against a library of Postgres-specific performance rules (H-01 to H-38).
- 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.
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
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 9 days ago