Initial Setup

When working with databases, especially with PostgreSQL, efficient data retrieval is crucial. For instance, consider a typical query where we fetch the latest entries from a table:

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

Initially, without any indexes, this query might 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 can 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, a composite index on both sensor_id and created_at brings it down dramatically to about 0.079ms. It’s also worth noting that in production settings, 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 can make the query even faster, 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 additional performance.

Conclusion

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