Re: strange index performance?

Scott Marlowe wrote:

> I'm guessing that you just had more data in the table or something by
> the time you tested that, or some cron job was running in the
> background, or some other issue, not the index.

It starts from scratch and builds up. Every insert has constant time
from the first to the last row, ie. row 1 to row 1.2 billion.
There is no background jobs or other disturbances.

> Quite a similar machine. write back cache with battery backed
> controller on the controller? A really old Areca like an 11xx series
> or a newer one 12xx, 16xx?

Its an Areca 1220. write back is enabled but it does not have a BBU,
because its an development machine and not a production machine.

> 0.12 seconds per insert is pretty slow. 10 inserts would take a
> second. I'm inserting 10,000 rows in about 2 seconds. Each insert is
> definitely in the 0.12 millisecond range.

I see the confusion. I use COPY(JDBC) not INSERT, so one transaction
contains 20000 rows, which is copy inserted in 300 ms, so that gives a
per row insert time of 0.015ms. So I actually have pretty decent write
performance. If I remove the index, the copy insert only takes about
125ms. So the index update time amounts to half the total update time.

This still leaves me with the question of why the smaller index
(id1,3,4) take longer to update than the larger index (id1,2,3,4)?
Updating an index like id1,2,3 should take shorter time, I have to test
it first to verify, so a similar index, id1,3,4 should take
approximately the same time.

Could it have something to do with the smaller index is more complicated
to fill in? Could the placing of the id2 filed in the table have
anything to say about it?

> Hard to say. What does bonnie++ have to say about the performance of
> your RAID array?

Dont know, havent heard about it before now. But I will have a look at
it and see if the controller and the os is set up correctly.

regards

thomas