Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Learn more
Home

index_advisor: query optimization

Index advisor is a Postgres extension for recommending indexes to improve query performance.

For example:


_10
select
_10
*
_10
from
_10
index_advisor('select book.id from book where title = $1');
_10
_10
startup_cost_before | startup_cost_after | total_cost_before | total_cost_after | index_statements | errors
_10
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------+--------
_10
0.00 | 1.17 | 25.88 | 6.40 | {"CREATE INDEX ON public.book USING btree (title)"},| {}
_10
(1 row)

Features:

  • Supports generic parameters e.g. $1, $2
  • Supports materialized views
  • Identifies tables/columns obfuscated by views
  • Skips duplicate indexes

Installation

index_advisor is a trusted language extension, which means it is directly installable by users from the database.dev SQL package repository.

To get started, enable the dbdev client by executing the setup SQL script.

Then, install index_advisor by running


_10
select dbdev.install('olirice-index_advisor');
_10
create extension if not exists hypopg;
_10
create extension "olirice-index_advisor";

API

Index advisor exposes a single function index_advisor(query text) that accepts a query and searches for a set of SQL DDL create index statements that improve the query's execution time.

The function's signature is:


_10
index_advisor(query text)
_10
returns
_10
table (
_10
startup_cost_before jsonb,
_10
startup_cost_after jsonb,
_10
total_cost_before jsonb,
_10
total_cost_after jsonb,
_10
index_statements text[],
_10
errors text[]
_10
)

Usage

As a minimal example, the index_advisor function can be given a single table query with a filter on an unindexed column.


_16
create extension if not exists index_advisor cascade;
_16
_16
create table book(
_16
id int primary key,
_16
title text not null
_16
);
_16
_16
select
_16
*
_16
from
_16
index_advisor('select book.id from book where title = $1');
_16
_16
startup_cost_before | startup_cost_after | total_cost_before | total_cost_after | index_statements | errors
_16
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------+--------
_16
0.00 | 1.17 | 25.88 | 6.40 | {"CREATE INDEX ON public.book USING btree (title)"},| {}
_16
(1 row)

and will return a row recommending an index on the unindexed column.

More complex queries may generate additional suggested indexes:


_55
create extension if not exists index_advisor cascade;
_55
_55
create table author(
_55
id serial primary key,
_55
name text not null
_55
);
_55
_55
create table publisher(
_55
id serial primary key,
_55
name text not null,
_55
corporate_address text
_55
);
_55
_55
create table book(
_55
id serial primary key,
_55
author_id int not null references author(id),
_55
publisher_id int not null references publisher(id),
_55
title text
_55
);
_55
_55
create table review(
_55
id serial primary key,
_55
book_id int references book(id),
_55
body text not null
_55
);
_55
_55
select
_55
*
_55
from
_55
index_advisor('
_55
select
_55
book.id,
_55
book.title,
_55
publisher.name as publisher_name,
_55
author.name as author_name,
_55
review.body review_body
_55
from
_55
book
_55
join publisher
_55
on book.publisher_id = publisher.id
_55
join author
_55
on book.author_id = author.id
_55
join review
_55
on book.id = review.book_id
_55
where
_55
author.id = $1
_55
and publisher.id = $2
_55
');
_55
_55
startup_cost_before | startup_cost_after | total_cost_before | total_cost_after | index_statements | errors
_55
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------------+--------
_55
27.26 | 12.77 | 68.48 | 42.37 | {"CREATE INDEX ON public.book USING btree (author_id)", | {}
_55
"CREATE INDEX ON public.book USING btree (publisher_id)",
_55
"CREATE INDEX ON public.review USING btree (book_id)"}
_55
(3 rows)

Limitations

  • index_advisor will only recommend single column, B-tree indexes. More complex indexes will be supported in future releases.
  • when a generic argument's type is not discernible from context, an error is returned in the errors field. To resolve those errors, add explicit type casting to the argument. e.g. $1::int.

Resources