Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is the speed of a PostgreSQL SELECT adversely affected by too many indexes on the table?

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 or 7 3 column indexes)?

EDIT: With performance drop I mean the performance in fetching rows (select), the database will be updated once a month so the update and insert time are not an issue.

like image 704
Topo Avatar asked Sep 27 '12 19:09

Topo


1 Answers

The indexes are maintained when the content of the table has been modified (i.e. INSERT, UPDATE, DELETE)

The query planner of PostgreSQL can decide when to use an index and when it's not needed and a sequential scan is more optimal.

So having too many indexes will hurt the modifying performance, not the fetching.

like image 197
KARASZI István Avatar answered Sep 21 '22 08:09

KARASZI István