Technical mailing list archives

Re: unused database indexes

Leonardo Rochael Almeida
- 07/27/2016 10:59:57

Resuscitating this old thread[1], I recently found this article that details very clearly why unused indexes are bad[2].

Notice that, despite the title of the article, I'm not advocating we consider MySQL.

But the article explains some details of the low level storage structure of Postgres and its implementation of MVCC that we should be aware of.

Basically, any write in any column of any record (even unindexed columns!) causes writes in ALL indexes pointing to that record.

In effect, any additional index on a PG table causes write amplification, so we should really make an effort to remove any unused or little used indexes.

And not add any index unless really needed.

Another point raised in the original thread was the fact that most CHAR field indexes are useless since most searches on these fields are through LIKE and ILIKE operators.

For these cases, we could be using trigram indexes: