index_advisor

pig build pkg index_advisor;		# build rpm/deb

pig repo add pgsql -u   # add both repo and update cache

pig install index_advisor;		# install via package name, for the active PG version

pig install index_advisor -v 18;   # install for PG 18
pig install index_advisor -v 17;   # install for PG 17
pig install index_advisor -v 16;   # install for PG 16
pig install index_advisor -v 15;   # install for PG 15
pig install index_advisor -v 14;   # install for PG 14

CREATE EXTENSION index_advisor CASCADE; -- requires hypopg

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[]
)

CREATE TABLE book (
    id    int  PRIMARY KEY,
    title text NOT NULL
);

SELECT * FROM index_advisor('
    SELECT book.id FROM book WHERE title = $1
');

SELECT * FROM index_advisor('
    SELECT book.id, book.title, publisher.name, author.name, 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
');