index_advisor

Overview

Version

Build

You can build the RPM / DEB packages for index_advisor using pig build:

pig build pkg index_advisor         # build RPM / DEB packages

Install

You can install index_advisor directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install index_advisor;          # Install for current active PG version

pig ext install -y index_advisor -v 18  # PG 18
pig ext install -y index_advisor -v 17  # PG 17
pig ext install -y index_advisor -v 16  # PG 16
pig ext install -y index_advisor -v 15  # PG 15
pig ext install -y index_advisor -v 14  # PG 14

dnf install -y index_advisor_18       # PG 18
dnf install -y index_advisor_17       # PG 17
dnf install -y index_advisor_16       # PG 16
dnf install -y index_advisor_15       # PG 15
dnf install -y index_advisor_14       # PG 14

apt install -y postgresql-18-index-advisor   # PG 18
apt install -y postgresql-17-index-advisor   # PG 17
apt install -y postgresql-16-index-advisor   # PG 16
apt install -y postgresql-15-index-advisor   # PG 15
apt install -y postgresql-14-index-advisor   # PG 14

Create Extension:

CREATE EXTENSION index_advisor;

Usage

index_advisor: Query index advisor

index_advisor analyzes a SQL query and recommends indexes that would improve its execution time, reporting before/after cost estimates and the DDL statements to create the suggested indexes.

Function

index_advisor(query text)
RETURNS TABLE (
    startup_cost_before jsonb,
    startup_cost_after  jsonb,
    total_cost_before   jsonb,
    total_cost_after    jsonb,
    index_statements    text[],
    errors              text[]
)

Basic Example

CREATE TABLE book (
    id    int  PRIMARY KEY,
    title text NOT NULL
);

SELECT * FROM index_advisor('
    SELECT book.id FROM book WHERE title = $1
');

Returns cost improvements and the recommended CREATE INDEX statement.

Multi-Table Example

SELECT * FROM index_advisor('
    SELECT book.id, book.title, publisher.name, author.name, review.body
    FROM book
    JOIN publisher ON book.publisher_id = publisher.id
    JOIN author    ON book.author_id = author.id
    JOIN review    ON book.id = review.book_id
    WHERE author.id = $1 AND publisher.id = $2
');

The output includes multiple index recommendations across the joined tables with before/after cost comparisons.

Features

• Supports parameterized queries using $1, $2 syntax
• Handles materialized views
• Resolves underlying tables and columns behind views
• Depends on HypoPG for hypothetical index evaluation