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.
Alternatively, you can use index_advisor directly via SQL.
For example:
_10select_10 *_10from_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
_10create 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:
_10index_advisor(query text)_10returns_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.
_16create extension if not exists index_advisor cascade;_16_16create table book(_16 id int primary key,_16 title text not null_16);_16_16select_16 *_16from_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:
_55create extension if not exists index_advisor cascade;_55_55create table author(_55 id serial primary key,_55 name text not null_55);_55_55create table publisher(_55 id serial primary key,_55 name text not null,_55 corporate_address text_55);_55_55create 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_55create table review(_55 id serial primary key,_55 book_id int references book(id),_55 body text not null_55);_55_55select_55 *_55from_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
index_advisor
repo