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:

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

Installation

To get started, enable index_advisor by running

1
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:

1
2
3
4
5
6
7
8
9
10
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[]    )

Usage

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create extension if not exists index_advisor cascade;create table book(  id int primary key,  title text not null);select  *from  index_advisor('select book.id from book where title = $1'); startup_cost_before | startup_cost_after | total_cost_before | total_cost_after |                  index_statements                   | errors---------------------+--------------------+-------------------+------------------+-----------------------------------------------------+-------- 0.00                | 1.17               | 25.88             | 6.40             | {"CREATE INDEX ON public.book USING btree (title)"},| {}(1 row)

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

More complex queries may generate additional suggested indexes:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
create extension if not exists index_advisor cascade;create table author(    id serial primary key,    name text not null);create table publisher(    id serial primary key,    name text not null,    corporate_address text);create table book(    id serial primary key,    author_id int not null references author(id),    publisher_id int not null references publisher(id),    title text);create table review(    id serial primary key,    book_id int references book(id),    body text not null);select    *from    index_advisor('        select            book.id,            book.title,            publisher.name as publisher_name,            author.name as author_name,            review.body 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    '); startup_cost_before | startup_cost_after | total_cost_before | total_cost_after |                  index_statements                         | errors---------------------+--------------------+-------------------+------------------+-----------------------------------------------------------+-------- 27.26               | 12.77              | 68.48             | 42.37            | {"CREATE INDEX ON public.book USING btree (author_id)",   | {}                                                                                    "CREATE INDEX ON public.book USING btree (publisher_id)",                                                                                    "CREATE INDEX ON public.review USING btree (book_id)"}(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