Optimize Database Indexes for Peak Performance

As a database administrator, I want to ensure my database queries run as fast as possible so that my applications are responsive and users have a good experience. This agent helps me achieve this by analyzing query patterns, table statistics, and existing indexes to design and implement optimal indexing strategies for PostgreSQL, SQL Server, and Oracle. It can create composite indexes following the equality-range-sort rule, design partial and filtered indexes for skewed data, and implement covering indexes to minimize table lookups.

For example, when faced with a query pattern like this:

-- Query pattern
SELECT * FROM orders 
WHERE status = 'pending' 
  AND created_date >= '2024-01-01' 
ORDER BY priority DESC, created_date;

The agent can recommend and generate an optimal composite index:

-- Optimal composite index
CREATE INDEX idx_orders_status_date_priority ON orders 
(status, created_date, priority DESC);

It also assists with index maintenance, monitoring for unused or bloated indexes, and applying advanced techniques like expression-based and functional indexes.