Show HN: PostgreSQL index advisor

417 points

2 years ago

This is a Postgres extension that can determine if a query should have an index. For example, for this table:

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

You can run `index_advisor()` to see if there should be an index on a select statement:

    select *
    from index_advisor('select book.id from book where title = $1');

And it will return (summarized):

    {"CREATE INDEX ON public.book USING btree (title)"}

It works particularly well with pg_stat_statements[0] which tracks execution statistics of all SQL statements executed on your Postgres database.

It leans heavily on HypoPG[1], an excellent extension to determine if PostgreSQL will use a given index without spending resources to create them.

97 comments