Index Optimization Loop
This loop iteratively improves database query performance by analyzing execution plans, identifying missing indexes, and implementing targeted index optimizations. Each iteration runs EXPLAIN ANALYZE on your query, identifies bottlenecks like sequential scans or costly sorts, and suggests or implements optimal indexes to reduce query execution time.
Goal
Improve database indexes
How to Run
Enter a SQL query and let the agent analyze its execution plan, identify missing indexes, and implement optimizations until the query runs efficiently.
- 01
Provide Query & Schema Info
Share the SQL query you want to optimize along with relevant table schema information including columns, data types, and existing indexes.
- 02
Agent Analyzes Execution Plan
The agent runs EXPLAIN ANALYZE to capture actual query performance metrics and identifies inefficiencies such as missing indexes or suboptimal join strategies.
- 03
Review Index Recommendations
Based on the analysis, the agent provides suggested indexes or modifications to existing indexes to improve performance.
- 04
Implement & Validate Changes
Create the recommended indexes, then re-run EXPLAIN ANALYZE to verify the query plan has improved. Exit once the plan shows acceptable performance.
Workflow Steps
- 01
Receive user's SQL query and schema context
- 02
Run EXPLAIN ANALYZE to examine current query plan
- 03
Identify performance bottlenecks (e.g., Seq Scan, Sort, Hash Join)
- 04
Propose specific index recommendations based on query patterns
- 05
Create suggested indexes using CREATE INDEX statements
- 06
Re-run EXPLAIN ANALYZE to confirm performance improvements
- 07
Compare execution times and plan costs to determine if optimization is complete
- 08
If not optimized and iterations remain, loop back to analyze new plan
- 09
Present final optimized query plan and implemented indexes upon completion
Kickoff Prompt
Start the "Index Optimization Loop" loop. Goal: Improve database indexes Max iterations: 10 Between iterations run: explain analyze Exit when: Optimized query plan Please provide the SQL query you'd like to optimize, along with the table schema (columns, data types, and existing indexes). Also share any known performance issues or specific goals for this query. Self-pace this loop. After each iteration, run `explain analyze` and evaluate the output, and only continue if the exit condition is not met (Optimized query plan). Stop when the exit condition passes or 10 iterations are reached. Give a short status update each pass.
Guardrails
hardcoded- ·Never execute on production databases without explicit approval
- ·Check index storage impact before creating on large tables
- ·Verify query correctness before and after index changes
- ·Avoid dropping indexes unless explicitly requested
- ·Consider concurrent index creation impact on ongoing transactions
- ·Validate that indexes are actually being used in the query plan
Flow Diagram
Related loops — Database
Database
Backup Verification
Automates the verification of database backup restorability by repeatedly testing restore operations until successful or maximum iterations reached.
Database
Database Schema Sync Loop
Automatically detects and resolves schema drift between your database and ORM models by iteratively applying necessary migrations and verifying alignment.
Database
Foreign Key Repair Loop
This loop identifies and repairs broken foreign key relationships in a relational database. It systematically detects constraint violations, proposes corrective actions, and validates fixes until all foreign key checks pass.