index_advisor

index_advisor is an extension that recommends indexes to improve performance of a given query.

Installation

Note:

hypopg is a dependency of index_advisor. Dependency resolution is currently under development. In the near future it will not be necessary to manually create dependencies.

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

Example

For a simple example, consider the following table:

Copycreate table book(
  id int primary key,
  title text not null
);

Lets say we want to query book by title, and return the relevant id. That query would be select book.id from book where title = $1.

We can get index_advisor to recommend indexes that would improve performance on that query as follows:

Copy
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
---------------------+--------------------+-------------------+------------------+-----------------------------------------------------
 0.00                | 1.17               | 25.88             | 6.40             | {"CREATE INDEX ON public.book USING btree (title)"},
(1 row)

where the output columns show top level statistics from the query explain plan (startup_cost, total_cost) and an array of index_statements that improve total_cost.

Features:

• Generic parameters e.g. $1, $2
• Support for Materialized Views
• Identifies Tables/Columns Oobfuscaed by Views

Usage

index_advisor is not limited to simple use cases. A more complex example could be:

Copyselect
    *
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
---------------------+--------------------+-------------------+------------------+----------------------------------------------------------
 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)"}
(1 row)

Note: the referenced tables must exist.

API

Copyindex_advisor(query text)
returns
    table  (
        startup_cost_before jsonb,
        startup_cost_after jsonb,
        total_cost_before jsonb,
        total_cost_after jsonb,
        index_statements text[]
    )

Description

For a given query, searches for a set of SQL DDL create index statements that improve the query's execution time;