Using indexes to speed up queries in PostgreSQL

In this post, I am going to explain how you can use indexes to speed up SELECT queries on a PostgreSQL database. Most of what this post covers will apply to any type of SQL database, but the syntax may be specific to PostgreSQL.

I spent the last month at work designing a new database to store chemical substances concentrations in rivers to provide data to the web application I manage. Finally I figured everything out: how many tables to create, how to connect them, which primary and foreign keys to use… and then I queried it from my web application and it took ages to return any result!

Had I spent a month working on something that would make my application unusable? Admittedly, the table that was giving me issues has almost 30 million rows, but everything I read online suggested that shouldn’t be a problem. So how to speed up my queries and make my application responsive?

I had already read about indexes, but I was convinced that since I already had a primary key covering multiple columns (including the one I was using to filter my slow query), it would not make a massive difference. How wrong I was! Adding an index for substance names to my table reduced query times from 23 to 0.4 seconds!

So when is adding an index to a table appropriate? Indexes will massively speed up SELECT queries that filter a fraction of the rows from a table. So if you’re going to use regularly queries that filter a large table based on one column, it makes sense to create an index for that column.
Creating an index for a table in PostgreSQL is very easy: just write

CREATE INDEX index_name ON table_name (column_name1, column_name2);

If you’re repeatedly querying a table for a small slice of rows, a partial index is probably a better choice. Partial indexes only cover a subset of a table’s data, are smaller in size, easier to maintain and faster to scan. In some cases, creating a full column index when you only want to get a small portion of the table can actually slow down your queries. The code to create a partial index in PostgreSQL is:

CREATE INDEX index_name ON table_name (column_name1) WHERE condition;

That’s it for now, hope it was useful!