Whether you are a developer, database administrator, or a manager, as long as you do something that needs to access database you will probably ask yourself if you need an index for your queries. And it’s for a good reason because you want your queries to run faster, as fast as they can so that the your users are happy and you keep your costs down and not wasting energy and CPU and I/O and so on.

The question is, how do you do it?

There are plenty of explanations on how to create good indexes to help your query performance, but for new people who are not familiar with database technologies, these things can be overwhelming and not so easy to understand.

Index recommendations in PostgreSQL

As I said earlier, there is no easy way to do this in Postgres because the output from EXPLAIN plan doesn’t give you the recommendations. You can see the path Postgres takes to execute the query, but there’s no such thing as “how to make it better”. You either understand how to read EXPLAIN plan output, which I encourage you to, or confused and Google it and hope for the best.

The good thing about Postgres is that there are plenty of extensions. You can do almost anything with Postgres with them, including spitting out index recommendations information.

pganalyze

pganalyze is a powerful product for Postgres with plenty of features. As the name suggests it can analyze your database to see what points you need to deal with to make your performance better. And obviously it can tell you what kind of indexes do you need to make your queries run faster. Apart from that it can also tell you what’s wrong with your database based on the log analysis.

However, pganalyze is not an open source tool. It consists of a few components that includes open source tools such as pganalyze-collector.

Postgres Workload Analyzer

PoWA team has been creating good products to help Postgres thrive and the main product is, you’ve guessed it, PoWA. PoWA is a performance tool to collect and analyze your database so you will have visibility and recommendations.

PoWA itself consists of a few tools, from collector, archivist, PoWA web interface, and statistics extensions. And all of these will give you everything.

But, if you only want index recommendations pg_qualstats the answer. pg_qualstats is a statistic extension to collect information of qual (predicates) found in WHERE and JOINS clauses in SQL statements. Since it collects all of these data more than in pg_stat_statements, it can tell you what kind of indexes needed for your query.

Here’s an example:

SELECT v
  FROM json_array_elements(
    pg_qualstats_index_advisor(min_filter => 50)->'indexes') v
  ORDER BY v::text COLLATE "C";
                               v
---------------------------------------------------------------
 "CREATE INDEX ON public.adv USING btree (id1)"
 "CREATE INDEX ON public.adv USING btree (val, id1, id2, id3)"
 "CREATE INDEX ON public.pgqs USING btree (id)"
(3 rows)

The only caveat is because it needs to get these predicates, it has to run in the background, which means it needs to be loaded during Postgres restart. So the extension has to be put in shared_preload_libraries parameter.

Index recommendations in CockroachDB

While it is a little bit complicated to have index recommendations in Postgres, it is much easier in CockroachDB. Since version 22.1, CockroachDB introduces index recommendations in the output of EXPLAIN plan by default. So, all you need is to upgrade your CockroachDB to v22.x and you’re good to go. I cannot vouch yet on the result of this feature, but considering this is built internally, I would assume that it is created based on proper statistics.

Here’s an example:

# EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;

                                                                       info
---------------------------------------------------------------------------------------------------------------------------------------------------
  distribution: full
  vectorized: true

  • sort
  │ estimated row count: 12,385
  │ order: +revenue
  │
  └── • filter
      │ estimated row count: 12,385
      │ filter: revenue > 90
      │
      └── • scan
            estimated row count: 125,000 (100% of the table; stats collected 19 minutes ago)
            table: rides@rides_pkey
            spans: FULL SCAN

  index recommendations: 1
  1. type: index creation
     SQL command: CREATE INDEX ON rides (revenue) STORING (vehicle_city, rider_id, vehicle_id, start_address, end_address, start_time, end_time);
(19 rows)

The recommendation can be disabled with SET index_recommendations_enabled TO false; in your session, just in case.

There you have it! Index recommendations tool to help you with your database.