Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I have read that when having a lot of indexes on a database It can seriously hurt the performance but in the PostgreSQL doc I can't find anything about it.

I have a very big table with something like 100 columns and a billion rows and often I have to do a lot of searches in a lot of different fields.

Does the performance of the PostgreSQL table will drop if I add a lot of indexes (maybe 10 unique column indexes and 5 to 7 three column indexes)?

EDIT: With performance drop I mean SELECT performance; the database will only be updated once per month so UPDATE and INSERT speed is not an issue.

share|improve this question

2 Answers

up vote 5 down vote accepted

No, the query performance will not be affected, or not very much. Indexes are updated on DML statements (and TRUNCATE) while they may or may not be used when executing a query.

The decision whether they will be used are made by the planner. With very many indexes present I can imagine that the planner spends more time on choosing the usable ones, but I would expect the difference to be small. (Normally, planning is faster that retrieving rows.)

Note that usually a few indexes will speed up a lot of different queries. This may not be the case if you filter on many of your columns.

Also note that in a lot of cases (and without knowing your table definition one cannot decide whether your case falls into 'most' or not) having so many columns reflects having a less-that-optimal schema design.

share|improve this answer
Usually, when someone says lots of indexes hurt performance, they're talking about insert, update, and delete statements. In short, anything that requires changing the indexes in addition to changing the tables. – Mike Sherrill 'Catcall' Oct 4 '12 at 21:18
This is mostly true, but what I've run into with a 'too many indexes' SELECT problem isn't so much that its additional plan time, but that the plan ends up with the less than optimal index being used. – rfusca Oct 5 '12 at 0:46
I would also wonder if caching of indexes in memory would hurt select performance in some cases by reducing effective cache size. – Chris Travers Oct 5 '12 at 2:59
@rfusca I made a joke lately about too many indexes confusing the planner - so it wasn't a joke? – dezso Oct 5 '12 at 5:08
1  
@Dezso - I've seen it happen, its rare - but it definitely happens. I don't think its 'confusing' so much as that for complex queries, the planner doesn't address every possible plan - so the more choices you give it, the more get cut out. Including possibly the best one. – rfusca Oct 5 '12 at 12:16
up vote 2 down vote

In general the thing you have to remember is that in PostgreSQL, index scans are not very expensive generally, but sequential scans are relatively cheap compared to some other databases (MySQL/InnoDB being a good example). So you don't necessarily want to index everything to start with.

In general I would not expect that excessive indexes would usually impact select performance but they will impact insert and update performance. As rfusca notes in his comment there is a danger of the planner choosing the wrong index. I would also think that if you have lots and lots of indexes, it may impact the effective caching, but with infrequent updates, that's probably less of a concern.

The typical advice is still good: add indexes when you need to and only when you need to. But overall chances are good that excessive indexes in your case will not hurt select performance.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.