index_advisor: query optimization
Index advisor is a Postgres extension for recommending indexes to improve query performance.
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)
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
_10select dbdev.install('olirice-index_advisor');_10create extension if not exists hypopg;_10create 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:
_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
errorsfield. To resolve those errors, add explicit type casting to the argument. e.g.$1::int.
Resources#
- Official
index_advisorGitHub Repository