The Complete Production SOP: How to Deploy and Run SQL Query & Index Optimizer in Autonomous AI Agents
Production SOP to install and run the SQL Query & Index Optimizer skill in Codex, Claude Code, Cursor, and Windsurf. Eliminate slow queries and N+1 joins.
Technical Overview & Architectural Context
Autonomous coding agents like Claude Code, Codex, Cursor, and Windsurf frequently introduce catastrophic database anti-patterns when left unconstrained. When asked to fix slow query performance, naive LLM agents often resort to adding single-column indexes on every WHERE clause column, blindly wrapping columns in SQL functions (breaking sargability), or replacing ORM queries with massive unindexed LEFT JOINs that exhaust connection pool memory. Worse, agents frequently propose unverified schema migrations without evaluating planner costs via EXPLAIN (ANALYZE, BUFFERS) or considering index write amplification on high-throughput OLTP workloads. The `sql-optimizer` skill installs deterministic standard operating procedures directly into the agent runtime. It forces the agent to read and evaluate execution plans, identify sequential scans on tables above threshold size, distinguish between B-tree, Partial, Expression, and GIN/GIST index requirements, and eliminate N+1 subqueries before modifying code. By establishing strict parameter boundaries, read-only explain sandboxes, and verification gates, `sql-optimizer` ensures the AI agent provides provable sub-5ms query plans without risking schema locks or table degradation.
Prerequisites & Environment Requirements
Verified Search Intents & Long-Tail Queries Solved
This operational tutorial is engineered to provide deterministic solutions for these developer search queries:
Step-by-Step Execution Playbook
Local Environment Setup & SKILL.md Mounting
Create the local skill manifest directory and inject verified procedural rules into agent runtime paths.
Autonomous runtimes discover skill capabilities through deterministic filesystem paths. For workspace-level discovery in Cursor, Windsurf, Codex, and Claude Code, install the skill into the local `.agents/skills/sql-optimizer` directory or symlink into runtime root rules (`.cursor/rules` or `.claude/skills`). The agent inspects the YAML frontmatter and loads the SOP during database diagnosis tasks.
mkdir -p .agents/skills/sql-optimizer && curl -fsSL https://askills.dev/api/skills/sql-optimizer/raw -o .agents/skills/sql-optimizer/SKILL.md---
name: "sql-optimizer"
description: "Diagnose slow queries using EXPLAIN ANALYZE, design B-tree/GIN indexes, and eliminate N+1 joins."
runtimes: ["codex", "claude-code", "cursor", "windsurf"]
category: "data"
triggers:
- "optimize query"
- "slow sql"
- "EXPLAIN ANALYZE"
- "fix n+1"
- "index design"
---
# SQL Query & Index Optimizer SOP
## Mandatory Diagnostic Protocol
1. Run `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)` for PostgreSQL or `EXPLAIN ANALYZE` for MySQL.
2. Identify execution bottlenecks: Sequential Scan (`Seq Scan`) with high row counts, `Nested Loop` with high loop counts, or high buffer shared hits/reads.
3. Rule out missing predicates and un-sargable conditions (`LOWER(email) = ...` or `DATE(created_at) = ...`).
4. Prefer composite indexes ordering by equality columns first, then range/sort columns (Equality-Range-Sort rule).
5. Verify that proposed index migrations include non-blocking creation directives (`CREATE INDEX CONCURRENTLY` in PostgreSQL or `ALGORITHM=INPLACE, LOCK=NONE` in MySQL).
6. Test the rewritten query and confirm buffer read reductions.Keep the SKILL.md file lean by omitting raw schema dumps; instead instruct the agent to query `information_schema` on demand to save token budget.
In Cursor and Windsurf, ensure project rules do not disable hidden directories, or map `.agents/skills` to `.cursor/rules/sql-optimizer.mdc`.
Environment Secrets & Diagnostic Sandbox Configuration
Configure isolated read-only credentials and execution guardrails for EXPLAIN ANALYZE operations.
Running EXPLAIN ANALYZE executes mutations if provided with `UPDATE` or `DELETE` statements. The skill mandates configuring read-only database connections or wrapping explain operations inside rolled-back transactions (`BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;`) to prevent agent-driven data mutations. Environment variables must be injected via workspace env files excluded from source control.
echo 'DATABASE_READONLY_URL="postgresql://readonly_analyst:[email protected]:5432/app_dev"' >> .env.local && git check-ignore -q .env.local || echo '.env.local' >> .gitignore#!/usr/bin/env bash
set -euo pipefail
QUERY="$1"
psql "$DATABASE_READONLY_URL" -c "BEGIN TRANSACTION READ ONLY; EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT) $QUERY; ROLLBACK;"Wrap all mutation query diagnoses in transaction rollbacks so write-heavy updates can be analyzed without side effects in staging/local environments.
Avoid exposing production write connection strings in `DATABASE_URL` during interactive agent sessions.
Intent Matching & Trigger Invocation
Trigger deterministic query diagnosis using optimized semantic prompts across Claude Code, Codex, and Cursor.
Invoke the skill using explicit trigger phrasing configured in the manifest. The agent recognizes keywords such as 'EXPLAIN ANALYZE' and 'eliminate N+1' to switch from general conversational response mode into deterministic database tuning mode.
@sql-optimizer Diagnose the slow user order lookup in `src/repositories/OrderRepository.ts`. Run EXPLAIN ANALYZE on the generated query, identify sequential scans, design a concurrent B-tree index, and batch the N+1 customer metadata fetch.Include the actual application file path in the prompt so the agent inspects the ORM abstraction (Prisma, TypeORM, Drizzle, or SQLAlchemy) alongside raw SQL.
Do not request index creation on small dev tables (<1000 rows) without instructing the agent to set `SET enable_seqscan = off;` for planner evaluation, or the query planner will default to seq scans regardless.
Verification, Linting & Output Quality Gate
Execute automated migration safety checks and performance regression validations before finalizing changes.
Before completing the task, the agent must run migration linters (e.g., `squawk` for PostgreSQL or atlas linting) and execute repository test suites to confirm semantic query equivalence and safety against table locks.
npx squawk migrations/*.sql && npm test -- test/integration/orders.test.ts-- Migration generated under sql-optimizer SOP
-- Prevents table locks using CONCURRENTLY
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);
-- Validate planner path with comment audit
-- Cost reduction target: >90% reduction in shared buffer readsInspect `git diff` to verify the agent did not introduce raw string interpolation or SQL injection vectors when rewriting ORM code.
PostgreSQL does not allow `CREATE INDEX CONCURRENTLY` inside an automated multi-statement migration transaction block unless migration engines (like Flyway or Prisma) are configured for non-transactional execution.
Real-World Scenario: Eliminating a 1.8-second Slow Query and N+1 Join in an E-Commerce Billing Route
“@sql-optimizer Analyze the checkout billing ledger query in `src/services/BillingService.ts`. The route takes 1850ms when querying unpaid invoices by organization. Inspect the query plan, resolve sequential scans, and refactor the ORM N+1 loop loading tenant line items.”
Runtime Troubleshooting & Failure Modes
Root Cause: The agent attempted to execute a concurrent index migration within an existing wrapper transaction created by the ORM migration runner.
Set migration flag or split migration. In Prisma: add `// @prisma-migrate-flags --non-transactional`. In Knex: set `exports.config = { transaction: false };`.Root Cause: Either the development database table contains too few rows for the planner cost calculation to justify index overhead, or the query uses a non-sargable function like `WHERE LOWER(email) = LOWER($1)` on a standard B-tree index.
Run `ANALYZE table_name;` and test with expression index: `CREATE INDEX idx_user_lower_email ON users (LOWER(email));` or force planner for testing: `SET enable_seqscan = OFF;`.Frequently Asked Questions (SQL Query & Index Optimizer SOP)
Q:Can I use SQL Query & Index Optimizer across Codex, Claude Code, and Cursor simultaneously?
Yes. The skill adheres to the universal SKILL.md specification. It can be placed at `.agents/skills/sql-optimizer/SKILL.md` or referenced in Claude Code (`.claude/skills`), Cursor rules (`.cursor/rules`), or Windsurf workflows without modification.
Q:How does SQL Query & Index Optimizer prevent context window overflow during long agent sessions?
Rather than dumping entire database schemas into the agent conversation, the skill enforces an on-demand inspection SOP where the agent only retrieves specific table schemas and EXPLAIN plans for the target query, conserving context tokens.
Q:Does this skill require escalated root or container permissions?
No. The skill operates with a 98/100 security score within standard workspace sandboxes. It requires standard CLI access to run query explanations and linters, operating via read-only transactions without needing root privileges.
Ready to equip your autonomous agent with SQL Query & Index Optimizer?
Copy the complete audited SKILL.md specification or download the 1-click zip bundle directly from our verified registry.