It is well-known that having an extra index leads to certain types of overhead:
- so-called write amplification, slowing odwn INSERTs and UPDATEs (DELETEs are not affected):
- not only writes are slowed down, but index changes are written to WAL, increasing its volume,
- thus, backups are also affected,
- and physical replication as well, more needs to be replicated;
- (a very obvious one!) extra disk space is used;
- overhead in memory – space in the buffer pool and page cache, occupied by index pages.
But it is much less known that extra indexes slow down SELECTs, and might even cause performance cliffs. Let's explore it.