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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
