Optimizer Usage

The Optimizer agent analyzes slow SQL queries and returns actionable recommendations. Submit a query and get back a structured execution plan, ranked rewrite alternatives validated by pglast for syntactic correctness, and index recommendations with storage and write-overhead estimates.

When RAG documents are available for your connection, the optimizer uses that business context to inform its analysis — understanding which columns are frequently queried together and which tables are performance-critical.

This guide walks through the full optimization workflow from query submission to applying recommendations.

Getting Started

1. Submit a query for optimization

   Send your SQL to the optimize endpoint:

   curl -X POST https://api.autodb.app/api/v1/connections/550e8400-e29b-41d4-a716-446655440000/queries/optimize \
     -H "Content-Type: application/json" \
     -H "X-API-Key: YOUR_API_KEY" \
     -d '{"sql": "SELECT o.id, c.name, SUM(oi.quantity * oi.unit_price) AS total FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items oi ON oi.order_id = o.id WHERE o.created_at > '\''2024-01-01'\'' GROUP BY o.id, c.name ORDER BY total DESC LIMIT 100;"}'

   The response includes three sections: execution_plan, alternatives, and index_recommendations:

   {
     "connection_id": "550e8400-e29b-41d4-a716-446655440000",
     "original_sql": "SELECT o.id, c.name, ...",
     "execution_plan": {
       "root": { "node_type": "Limit", "total_cost": 1510.0, "children": [ /* ... */ ] },
       "raw_plan_text": "Limit  (cost=1500.00..1510.00 rows=100 width=64)\n  ->  Sort  ...",
       "total_cost": 1510.0,
       "analyzed": false
     },
     "alternatives": [
       {
         "rank": 1,
         "rewritten_sql": "SELECT o.id, c.name, ...",
         "explanation": "Replace SELECT * with specific columns to reduce I/O.",
         "estimated_improvement": "~1.5-2x faster"
       }
       // ...
     ],
     "index_recommendations": [
       {
         "create_sql": "CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at);",
         "reason": "Sequential scan on orders filtered by created_at",
         "estimated_storage_impact": "~50MB",
         "detection_method": "rule_based"
         // ...
       }
     ],
     "context_used": true
   }

2. Review the execution plan

   The execution_plan field contains the parsed PostgreSQL EXPLAIN output as a structured tree. The root node has a node_type (e.g., "Seq Scan", "Index Scan", "Hash Join"), cost estimates, and children for nested operations. Walk the tree to understand where time is spent.

   The raw_plan_text field contains the original EXPLAIN output from PostgreSQL for quick visual inspection. The total_cost field gives the overall estimated cost in PostgreSQL cost units.

3. Evaluate rewrite alternatives

   The alternatives array contains ranked query rewrites. Each entry includes:

   • rank — position in the ranking (1 = best recommendation)
   • rewritten_sql — the optimized query
   • explanation — plain-English description of what was changed and why
   • estimated_improvement — rough performance estimate (e.g., "~1.5-2x faster")

   All rewrites are validated with pglast for syntactic correctness before inclusion. Invalid rewrites are filtered out automatically.

4. Consider index recommendations

   The index_recommendations array suggests indexes that could improve your query. Each entry includes:

   • create_sql — a ready-to-execute CREATE INDEX statement
   • reason — why this index would help
   • estimated_storage_impact — approximate additional storage needed (e.g., "~50MB")
   • write_overhead_warning — impact on write performance (e.g., "Adds ~5-10% overhead on INSERT/UPDATE")
   • benefits — list of query patterns that would benefit from this index
   • detection_method — either "rule_based" (heuristic detection of sequential scans) or "llm_suggested" (AI-generated recommendation)

   Tip

   All recommended CREATE INDEX statements use CONCURRENTLY to avoid blocking writes. You can execute them during normal operations without downtime.

Using EXPLAIN ANALYZE

Add ?analyze=true to the query string to get real timing data alongside estimates. This runs EXPLAIN ANALYZE on your database, which provides actual_startup_time, actual_total_time, actual_rows, and actual_loops for each plan node.

curl -X POST "https://api.autodb.app/api/v1/connections/550e8400-e29b-41d4-a716-446655440000/queries/optimize?analyze=true" \
  -H "Content-Type: application/json" \
  -H "X-API-Key: YOUR_API_KEY" \
  -d '{"sql": "SELECT id FROM orders WHERE created_at > '\''2024-01-01'\'';"}'

Caution

EXPLAIN ANALYZE actually executes the query. Use with caution on production databases with write operations.

Context-Aware Optimization

When context_used is true in the response, the optimizer used RAG document context to inform its analysis. Uploaded business documentation helps the optimizer understand which columns are queried together, which tables are performance-critical, and what access patterns your application uses — resulting in more relevant recommendations.

Next Steps

Query Optimization Reference Execution plans, rewrite alternatives, and index recommendations in detail