index_advisor

Overview

Upstream 0.2.1; pkg 0.2.0; planner estimates only.

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:

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 CASCADE;  -- requires: hypopg

Usage

Sources:

• Official source
• Official source
• Official source

index_advisor 0.2.1 uses HypoPG to compare planner costs with hypothetical indexes. It returns recommendations, not measured execution-time guarantees, and does not create the recommended physical indexes.

Core Workflow

Install the required hypopg extension first, with the privileges its installation requires. This SQL extension needs no preload or restart.

CREATE EXTENSION hypopg;
CREATE EXTENSION index_advisor;
CREATE TABLE book (id bigint PRIMARY KEY, title text NOT NULL);
SELECT * FROM index_advisor('SELECT id FROM book WHERE title = $1');

index_advisor(query text) accepts a parameterized query and returns before/after startup and total cost values, index_statements and errors. Inspect the suggested DDL and real workload before creating an index. View and materialized-view support depends on what the advisor can resolve to underlying tables.

0.2.1 Corrections and Limits

Version 0.2.1 fixes qualification of HypoPG calls when HypoPG uses another schema, non-greedy removal of comments, and cleanup of the advisor’s own prepared statement. Update installed databases with ALTER EXTENSION index_advisor UPDATE TO '0.2.1' after installing the new SQL files.

Run analysis with deliberate table permissions and representative statistics. Planning cost does not account for every operational cost: additional indexes consume disk and slow writes, and should be validated against production query shapes before deployment.