Overview

Cast AI Index Advisor analyzes your PostgreSQL database workloads and provides intelligent recommendations for index optimization. By examining query patterns, execution plans, and table statistics, Index Advisor identifies opportunities to improve query performance through strategic index creation.

How Index Advisor Works

Index Advisor operates through a lightweight agent that connects to your PostgreSQL database and collects metadata for analysis:

1. Query Analysis - The agent collects query statistics from pg_stat_statements to understand your actual workload patterns
2. Execution Plan Collection - For queries that could benefit from optimization, the agent gathers execution plans to understand how PostgreSQL processes them
3. Metadata Collection - Table structures, existing indexes, and column statistics are collected to inform recommendations

Key Features

Automated Discovery

Index Advisor automatically identifies:

• Missing indexes that could accelerate frequent queries
• Unused indexes that consume storage without providing benefit
• Partial index opportunities for filtered queries

Non-Intrusive Collection

The Index Advisor agent is designed to minimize impact on your production database:

• Read-only access - The agent only reads system catalogs and statistics
• No query modification - Your application queries are never altered
• Lightweight footprint - Runs as a small Kubernetes deployment

Actionable Recommendations

Each recommendation includes:

• The specific CREATE|DROP INDEX statement to implement
• Expected performance impact based on query patterns
• Clear reasoning for why the recommendation was generated

Relationship with DBO Cache

Index Advisor and DBO Cache are independent products that complement each other. You can use either one alone, or both together for comprehensive database optimization.

When used together, these tools address optimization at different layers: Index Advisor improves query execution at the database level, while DBO Cache reduces round-trips to the database entirely for repeated queries.

Prerequisites

Before enabling Index Advisor, ensure you have:

• PostgreSQL database with pg_stat_statements extension enabled
• Network connectivity from your Kubernetes cluster to the database
• A database user with read-only access to system catalogs

Getting Started

Ready to optimize your database indexes? Follow the installation guide to deploy the Index Advisor agent.