Every Extra Index Is a Tax on Every Insert, Update and Delete
Each index is a second sorted copy of some columns, so every row you write has to be written into each of them too, and each of those writes generates more log and more pages to keep in memory. A table with ten indexes can be several times slower to load than the same table with two, and the extra indexes also enlarge the planner's search. The rule is that an index has to pay for itself with the reads it serves.
Questions this Concept answers
Why does adding a ninth and tenth index to a busy table slow down updates and deletes, not just inserts?
J
jeremy
Video
The Write, Bloat, and Locking Costs of Adding an Index in PostgreSQL
An index in PostgreSQL is a secondary, sorted data structure — typically a B-tree — maintained alongside the table, whose shallow branching depth converts a full sequential scan into a few page reads…