Very long times to build hash indexes
От | David Monarchi |
---|---|
Тема | Very long times to build hash indexes |
Дата | |
Msg-id | eea51fdb0703201051w7b557752i12502e0f4cd5a9c6@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Very long times to build hash indexes
|
Список | pgsql-novice |
Hello -
We have a database of about 250GB. The core table contains about 140M rows that are all integers and small integers. Aside from the key field, the rest are all foreign keys. Virtually all of our queries use equalities rather than inequalities/ranges. The database changes at a fairly even rate due to insertions and deletions. Updates are rare. Insertions dominate the environment, and we expect to have about 400M rows in the core table by the end of the year.
We need to build indexes on 10 foreign key fields in the core table. Based on the type of queries and the fact that insertions in it are fast, we are building hash indexes on those fields. We have successfully built 5 of the 10 hash indexes. Each one required about 20 hours to construct.
When we got to the 6th field, we found that the indexing process would not terminate even after 70 hours. We then tried the 7th field with the same result. Is there something that we've overlooked? Is there a limit of some kind that we've missed?
Any advice/suggestions would be very much appreciated.
Thank you.
david
We have a database of about 250GB. The core table contains about 140M rows that are all integers and small integers. Aside from the key field, the rest are all foreign keys. Virtually all of our queries use equalities rather than inequalities/ranges. The database changes at a fairly even rate due to insertions and deletions. Updates are rare. Insertions dominate the environment, and we expect to have about 400M rows in the core table by the end of the year.
We need to build indexes on 10 foreign key fields in the core table. Based on the type of queries and the fact that insertions in it are fast, we are building hash indexes on those fields. We have successfully built 5 of the 10 hash indexes. Each one required about 20 hours to construct.
When we got to the 6th field, we found that the indexing process would not terminate even after 70 hours. We then tried the 7th field with the same result. Is there something that we've overlooked? Is there a limit of some kind that we've missed?
Any advice/suggestions would be very much appreciated.
Thank you.
david
В списке pgsql-novice по дате отправления: