jeudi 26 mars 2015

Unique Index much bigger than the table itself


Pg 9.3 on debian 7.


I have a very huge table in rows, about 178 million (will turn 250 million in a week and then grow 500 thousand a day), but not that huge in size, wich is 20GB, it's a small varchar, some timestamps and integers. I need to prevent a group of 5 columns to be inserted repeated, so I created an unique index on them. The problem is that the index is 34GB now. My questions are: How can an unique index of 5 columns in a table of 9 be bigger than the full amount of data itself? Will this index ever be used, cause I guess it's cheaper for the DBMS to scan the entire table instead. And is there any way to turn this index smaller or maybe have another solution to implement an unique constraint?





Aucun commentaire:

Enregistrer un commentaire