On Mon, Jan 26, 2009 at 10:10:13AM +0100, Thomas Finneid wrote:
> 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?
>
It may be that the smaller index has update contention for the same
blocks that the larger index does not.
Cheers,
Ken
