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

Post featured image

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:

 

[gist id=85fa86e94c79febd1b95]

 

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:

 

screen-shot-2014-06-03-at-15-58-56-1024x307

 

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