- Home
- Skills
- Data & Databases
- sql optimization patterns
Works with the AI Agents you already use
sql optimization patterns
Optimize SQL performance through EXPLAIN plan analysis, index design, and query refactoring.
$8
sql optimization patterns
Example session with this skill installed
I have a slow query on PostgreSQL. Table 'orders' has 2M rows. Query: SELECT * FROM orders WHERE status = 'pending' AND created_at > '2023-01-01' ORDER BY created_at DESC. Here is the EXPLAIN plan.
- Read your context and instructions
- Compiled the sql optimization patterns
The analysis indicates a Parallel Seq Scan on the orders table because the current index on created_at is not being utilized efficiently alongside the status filter. The query cost is high due to the filter operation occurring after the disk read.
SQL Optimization Report
| Component | Finding | Recommendation |
|---|---|---|
| Current Plan | Parallel Seq Scan on orders (cost=0.00..45823.10) | The planner is ignoring the single-column index on created_at due to the status filter. |
| Bottleneck | Filter: (status = 'pending'::text) | 85% of execution time is spent filtering rows in memory after a full table scan. |
| Index Strategy | Missing Composite Index | Create a composite index covering both the filter and sort columns to allow an Index Scan. |
| Query Rewrite | Selectivity Issues | Replace SELECT * with specific columns to reduce memory overhead and potentially allow an Index Only Scan. |
Proposed Index
CREATE INDEX idx_orders_status_created_at_desc
ON orders (status, created_at DESC);
Performance Validation Plan
- Baseline: Run
EXPLAIN ANALYZEon the current query three times to establish a median execution time. - Apply Change: Execute the
CREATE INDEXstatement (preferablyCONCURRENTLYin production).
Verify: Run EXPLAIN ANALYZE again. Expected result is an Index Scan or Index Only Scan using idx_orders_status_created_at_desc.
4. Rollback: DROP INDEX idx_orders_status_created_at_desc; if execution time does not decrease by at least 40%.
Next steps
- Provide the output of
\d ordersto confirm there are no conflicting indexes. - Run the baseline
EXPLAIN ANALYZEand share the total execution time in milliseconds. - Confirm if this table receives high write volume, as additional indexes impact
INSERTperformance.
Connects securely to your tools. The creator never sees your data.
About this skill
The problem
Slow SQL queries drag down application performance and increase cloud costs. Identifying the exact cause in an EXPLAIN plan or designing the correct composite index is time-consuming and prone to error.
What it does
- Analyzes EXPLAIN and EXPLAIN ANALYZE output to identify bottleneck operations like sequential scans or costly joins.
- Audits existing indexing strategies and proposes specific composite, partial, or functional indexes based on query predicates.
- Detects N+1 query patterns in application code and suggests batching or join-based rewrites.
- Provides structured optimization reports including a baseline measurement, proposed changes, and a validation plan.
Frameworks & tools
PostgreSQL, MySQL, MariaDB, and SQL Server. Compatible with ORMs like Eloquent, TypeORM, and SQLAlchemy for identifying N+1 issues.
Why this beats prompting it yourself
Generic LLM prompts often suggest "add an index" without checking existing constraints or table cardinality. This skill enforces a strict evidence-based workflow, requiring plan analysis before making recommendations to avoid redundant indexes and production regression.
Use cases
- Triaging slow queries reported in APM tools like New Relic or Datadog.
- Reviewing schema migrations to ensure new features have appropriate indexing from day one.
- Refactoring legacy SQL queries that have slowed down as table volume reached millions of rows.
Known limitations
Requires table schema or cardinality information to provide accurate cost estimates. Cannot execute changes directly on production databases.
Requires EXPLAIN plan output for accurate analysis. Cannot perform destructive actions or schema changes without manual review and approval.
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 2 months ago
Permissions
No special permissions declared or detected