I have a data collector function in a PostGreSQL 7.4 DB running on Linux that inserts approximately 10000 records into a table every fifteen minutes. The table has two macaddr columns, one varchar(50) column, two timestamptz columns, five interval columns, one float8 column, and one int4 column. I have one multi-column B-tree index on the two macaddr columns, the varchar(50), and one of the timestamptz columns, in that order.
The 10000-record insert takes approximately 2 minutes, which I thought seemed awfully slow, so I tried removing the index, and sure enough, without the index the insert took less than two seconds. I repeated the inserts many times (with and without the index) and there’s very little other activity on this server, so I’m confident of these results.
There are approximately 10000 fixed combinations of the first three indexed columns, and the fourth is the current time, so essentially what the function is doing is inserting a set of values for each of those 10000 fixed combinations for every fifteen minute period. I can see how this might be a worst-case scenario for an index, because the inserted rows are alone and evenly spaced through the index. Even so, it doesn’t seem reasonable to me that an index would slow an insert more than 50-fold, regardless of hardware or the nature of the index. Am I wrong? Can anybody suggest why this would be happening and what I might be able to do about it? In production the table will have several million records, and the index is necessary for data retrieval from this table to be feasible, so leaving the index off is not an option.
Thanks in advance,
Trevor Ball