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?

id_process_publication bigint
search_token character varying(25)
publication_date date
publication_page integer
id_publication_site integer
id_publication_book integer
insert_time timestamp
integration_consume_time timestamp
elastic_consume_time timestamp

"process_publication_pkey" PRIMARY KEY, btree (id_process_publication)
"idx_uniq_process_publication" UNIQUE, btree (search_token, publication_date, publication_page, id_publication_site, id_publication_book)
"idx_uniq_process_publication_where_elastic_consume_time_null" UNIQUE, btree (id_process_publication) WHERE elastic_consume_time IS NULL
"idx_uniq_process_publication_where_integration_consume_time_nul" UNIQUE, btree (id_process_publication) WHERE integration_consume_time IS NULL
  • I have an idea, but before i try to answer please provide these two informations: Primary key columns and the datatypes Unique index columns and the datatypes Thank you – Vladislav Zalesak Mar 26 '15 at 20:20
  • @VladislavZalesak Edited! – Ivan De Sousa Paz Mar 26 '15 at 20:34
  • But can the DBMS use an index reading it partially? Because in a very simple way of thinking, if the DBMS needs to read it entirely, reading the data pages itself to look for the collision costs less. – Ivan De Sousa Paz Mar 26 '15 at 20:57
  • 1
    The size for this index should not normally be bigger than the table. There may be bloat, while the table has been vacuumed. How big is the index after you drop / recreate it? – Erwin Brandstetter Mar 27 '15 at 8:34
  • @IvanDeSousaPaz the index is organized like a tree, and (provided that the order of columns in its definition makes sense, for example follow the most-selective-first approach) can significantly reduce the number of pages to be visited. Look at this page to have an idea how. And yes, you need the index for maintaining uniqueness. – dezso Mar 27 '15 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.