indexes greatly slowing data entry
От | DE Sesa, Michael C. |
---|---|
Тема | indexes greatly slowing data entry |
Дата | |
Msg-id | AFAB5F8466DAD411B586000255B03E1416CCC1@mobntxch12.peco.com обсуждение исходный текст |
Ответы |
Re: indexes greatly slowing data entry
Re: indexes greatly slowing data entry |
Список | pgsql-general |
I'm having a problem with performance and indexing seems to be the cause. I have a database with two tables with exactly the same structure. There are two fields in each which I need to have indexed. One is a varchar(12), the other a timestamp. Data comes from another computer as an ASCII delimited text file at about 20,000 records per hour. Everything was fine until the database grew to about 10 Gigabytes, then the computer could no longer keep up with putting the data into the postgres database (using a perl script with DBI.) I found that when I dropped one of the indexes on varchar, the problems went away. It did not matter from which table I dropped the index. With both varchar indexes in place, data for the table with the varchar index created second would take 10 times as long to store data into compared to the other table. From vmstat: blocks in (bi) is 10 times higher with both varchar indexes when compared to with only one, and it looks like the raid array is maxed out. The indexes on timestamp did not seem to impinge on performance at all. There is no swapping to/from disk going on. Can anyone help me figure out what is the problem/solution? I do an automated vacuum analyze nightly. Performance is not increased afterward. pg 7.0.3 on Red Hat Linux 7.1. ************************************************************************ This e-mail and any of its attachments may contain Exelon Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Exelon Corporation family of Companies. This e-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this e-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this e-mail is strictly prohibited and may be unlawful. If you have received this e-mail in error, please notify the sender immediately and permanently delete the original and any copy of this e-mail and any printout. Thank You. ************************************************************************
В списке pgsql-general по дате отправления: