index_advisor

index_advisor : Query index advisor

Overview

Packages

Source

pig build pkg index_advisor;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install index_advisor;		# install via package name, for the active PG version

pig install index_advisor -v 18;   # install for PG 18
pig install index_advisor -v 17;   # install for PG 17
pig install index_advisor -v 16;   # install for PG 16
pig install index_advisor -v 15;   # install for PG 15
pig install index_advisor -v 14;   # install for PG 14

Create this extension with:

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.