Database

index_advisor: query optimization

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

Features:

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

index_advisor is accessible directly through Supabase Studio by navigating to the Query Performance Report and selecting a query and then the "indexes" tab.

Supabase Studio index_advisor integration.

Alternatively, you can use index_advisor directly via SQL.

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)

Installation

To get started, enable index_advisor by running


_10
create extension 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