Index Slowing Insert >50x

From: "Trevor Ball" <TBall(at)eppcon(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Index Slowing Insert >50x
Date: 2005-01-31 21:14:39
Message-ID: E5F77E3E43D87C4D9A9ED35E1A31814B0AAC8C@mink.eppcon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2005-01-31 21:35:08 Re: High end server and storage for a PostgreSQL OLTP system
Previous Message Cosimo Streppone 2005-01-31 20:41:32 High end server and storage for a PostgreSQL OLTP system