index_advisor - Improve SQL Efficiency with PostgreSQL Index Optimization

PostgreSQL Index Advisor

The PostgreSQL Index Advisor is an extension designed to enhance the performance of database queries by suggesting optimal index strategies. With the Index Advisor, users can optimize their PostgreSQL databases by incorporating well-planned indexes that improve query access times and resource efficiency.

Features

Parameter Support: The Index Advisor supports generic SQL parameters such as $1, $2, ensuring compatibility with a wide range of queries.
Materialized Views: This tool is capable of working with materialized views, which are often used to improve the performance of complex reports and analytics.
View Obfuscation Handling: It can identify tables and columns within views, even when they are obfuscated, to suggest the most efficient indexing.

API Description

The core function of the Index Advisor is to analyze a given SQL query and provide recommendations through a set of create index SQL DDL statements. These suggested indexes aim to enhance the execution time of the query.

Function Signature:

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 Examples

The Index Advisor can be used for both simple and complex queries. Here are a couple of examples:

Simple Query Example:

Consider a simple query that filters data from a single table with an unindexed column. By running the index_advisor function, users can receive suggestions for creating an index to improve performance.

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');

This example would generate output suggesting the creation of an index on the title column of the book table.

Complex Query Example:

For more intricate queries involving multiple joins and conditions, the Index Advisor can provide several index recommendations.

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
    ');

In this example, the Index Advisor might recommend creating indexes on columns such as author_id, publisher_id, and book_id in the relevant tables.

Installation

To install the PostgreSQL Index Advisor, users need a PostgreSQL database with the HypoPG extension installed. The installation process involves cloning the project’s repository and running installation commands.

git clone https://github.com/supabase/index_advisor.git
cd index_advisor
sudo make install

Running Tests

To ensure the installation and functionality are correctly set up, users can run the provided tests using the following commands:

make install; make installcheck

The PostgreSQL Index Advisor is a powerful tool for database administrators and developers looking to fine-tune their database indexing strategies for improved query performance.