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.
************************************************************************
