Database Query Optimiser Agent

Slow database queries are one of the most common performance bottlenecks in production systems. A query optimisation agent can analyse a slow query, interpret the EXPLAIN output, identify missing indexes or suboptimal joins, and produce an optimised rewrite with clear reasoning - turning a task that takes a senior DBA hours into a matter of seconds.

In this tutorial, we build a ShopMax India query optimiser agent that takes a slow PostgreSQL query and its EXPLAIN ANALYZE output, diagnoses the bottleneck, and returns an optimised version with the recommended indexes to create.

The below example shows the agent diagnosing a slow orders report query and producing an optimised rewrite.

It gives the following output,

## Query Analysis - ShopMax India Orders Report

### Bottlenecks Identified

1. Seq Scan on orders (400,000 rows) - No index on (status, created_at, customer_id)
2. Seq Scan on customers (80,000 rows) - No index on city column
3. 350,000 rows filtered AFTER scan - indexes would eliminate most of this work

### Recommended Indexes

CREATE INDEX idx_orders_status_date_customer
    ON orders (status, created_at, customer_id)
    WHERE status = 'delivered';

CREATE INDEX idx_customers_city ON customers (city);

### Optimised Query

SELECT c.name, c.city,
       SUM(o.total_amount) AS lifetime_value,
       COUNT(o.id) AS order_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
                 AND c.city IN ('Mumbai', 'Bangalore', 'Delhi', 'Hyderabad')
WHERE o.status = 'delivered'
  AND o.created_at >= '2024-01-01'
GROUP BY c.name, c.city
ORDER BY lifetime_value DESC
LIMIT 100;

Changes: orders as leading table (smaller filtered set), city filter pushed into JOIN.

### Estimated Improvement
Current: 8,432 ms | Estimated after indexes: 180-320 ms (25-45x improvement)

Feed this agent your slow query log from pg_stat_statements to continuously find and fix the worst-performing queries. For production databases, always test the rewritten query on a staging environment with EXPLAIN ANALYZE before deploying. Creating indexes with CONCURRENTLY avoids locking the table during index build.