PostgreSQL Index usage

Dev Bit: Are You Using All Indexes of Your PostgreSQL Database?

Helmut Development, LingoHub Translate 1 Comment

CEO and founder of Lingohub. Envisioning a multilingual digital world. Email me if you have questions about how Lingohub can help you take your products global.

We all know that an index can speed up database queries enormously. However, indices do not come for free. They create an overhead for every write and need quite some space. So in a best case scenario, you would like to have only those indices that your application actually uses. If your database is PostgreSQL, there is a simple way to print out statistics of the actual index usage:



Run this query against your database and you will find your unused indices in a heartbeat. Here is a screenshot from running the query in pgAdmin:


PostgreSQL Index usage


If you have similar queries for other databases, feel free to share them in the comments.