Number of indexes per table

Question

1. Based on your experience, is there any practical limit on the number of indexes per one table in Postresql? In theory, there is not, as per the documentation, citation: "Maximum Indexes per Table Unlimited" But:

2. Is it that the more indexes you have the slower the queries? Does it make a difference if I have tens vs hundreds or even thousands indexes? I am asking after I've read the documentation on postgres' partial indexes which makes me think of some very creative solutions that, however, require a lot of indexes.

Answer 1

There is overhead in having a high number of indexes in a few different ways:

1. Space consumption, although this would be lower with partial indexes of course.
2. Query optimisation, through making the choice of optimiser plan potentialy more complex.
3. Table modification time, through the additional work in modifying indexes when a new row is inserted, or current row deleted or modified.

I tend by default to go heavy on indexing as:

1. Space is generally pretty cheap
2. Queries with bound variables only need to be optimised once
3. Rows generally have to be found much more often than they are modified, so it's generally more important to design the system for efficiently finding rows than it is for reducing overhead in making modifications to them.
4. The impact of missing a required index can be very high, even if the index is only required occasionally.

I've worked on an Oracle system with denormalised reporting tables having over 200 columns with 100 of them indexed, and it was not a problem. Partial indexes would have been nice, but Oracle does not support them directly (you use a rather inconvenient CASE hack).

So I'd go ahead and get creative, as long as you're aware of the pros and cons, and preferably you would also measure the impact that you're having on the system.