Supabase has launched Index Advisor, a Postgres extension for recommending indexes that improve query performance, easing the burden off the developer's shoulders.

Usually when you create your tables you'll also want to create indexes upon them for rapid retrieval. In order to do that you'll need a good understanding of your domain and how the code accesses data. RDBMs's are optimized to work declaratively; that is you tell them what you want without instructing them how to, like you do in the imperative programming model, letting it decide the best way of going about it.

That goes also for index selection. While you might have diligently placed your indexes, the query engine might not follow them but instead do a full table scan if it finds that this is a better approach. However, hinting at the engine which path to follow by creating an index is a deviation from the declarative model.

What if you could reverse this process and go full on declarative? that is let the engine to create the index for you by examining your queries. And that's exactly what the new "index advisor" extension from Supabase does. You feed it with the sql code that your application would fire and let the advisor recommend you what indexes to build.

An example will make everything clear. First you've got to tell Postgres to use the extension:

create extension if not exists index_advisor cascade;

Then feed it with your query:

select * from index_advisor

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

In this instance, the advisor will suggest the following:

including the estimated improvement in startup and total costs:

Startup cost is the cost to fetch the first row, Total cost is the cost to fetch all the rows. Costs are in arbitrary units, where a single sequential page read costs 1.0 units.

The example above is a simple one , but the advisor can work with more complex queries that may generate additional suggested indexes :

As it is right now, the index_advisor will only recommend single column, B-tree indexes. More complex indexes will be supported in future releases. It supports :

• Generic parameters e.g. $1, $2
• Supports materialized views
• Identifies tables/columns obfuscated by views
• Skips duplicate indexes

The advisor leans heavily on the hypopg (Hypothetical Indexes) extension which has to be installed before it. This is necessary because HypoPG estimates the cost of a query if a given (hy-pothetical) index existed, without actually creating that index.

Find it on its Github repo or as an option on your Supabase dashboard.

More Information

PostgreSQL Index Advisor

Related Articles

pgxman - PostgreSQL Extension Manager

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info