High Performance Indexing in PostgreSQL

Initial Setup

When working with Rails apps, fast data retrieval is important part of the app’s overall performance.

Consider this query where we fetch the latest entries from a table:

SELECT * FROM pings WHERE sensor_id = 11 ORDER BY created_at DESC LIMIT 65

Without any indexes, this query will be slow, taking about 16ms as it scans the entire table.

Single Column Index

Now with a cost baseline, let’s apply a common index optimization.

Adding an index on sensor_id will reduce the query time to around 13ms—a small 3 ms improvement.

CREATE INDEX index_pings_on_sensor_id ON pings (sensor_id)

Multi-column Index

To further reduce the time, add a composite index on both sensor_id and created_at.You’ll see that the new composite index brings down the execution time to 0.079ms.

It’s also worth noting that in production environments, composite indexes are generally preferred over single column indexes. This is because postgresql can use these composite indexes in place of single column indexes too.

 CREATE INDEX index_pings_on_sensor_id_created_at ON pings (sensor_id, created_at)

Ordered Index

Optimizing further by ordering the created_at column in descending order in our index will make the query even faster, with execution time coming in at about 0.067ms

CREATE INDEX index_pings_on_sensor_id_create_at_desc on pings (sensor_id, created_at desc);

By applying a compound index on sensor_id and created_at in descending order for the pings table, we achieve a notable reduction in query cost. The result is an execution time of 0.067 ms, squeezing out just a little more performance.

By improving indexing strategies from a basic single column to a more ordered composite index, we’ve improved the query speed significantly, from 16ms down to under a millisecond. These indexing strategies are practical implementations that can lead to substantial performance gains in real-world scenarios.