Skip site navigation (1) Skip section navigation (2)

Re: strange index performance?

From: Kenneth Marshall <ktm(at)rice(dot)edu>
To: Thomas Finneid <tfinneid(at)fcon(dot)no>
Cc: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>,pgsql-performance(at)postgresql(dot)org
Subject: Re: strange index performance?
Date: 2009-01-26 13:50:59
Message-ID: 20090126135059.GT1961@it.is.rice.edu (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Thomas FinneidDate: 2009-01-26 14:49:00
Subject: Re: strange index performance?
Previous:From: Matthew WakelingDate: 2009-01-26 12:18:23
Subject: Re: postgresql 8.3 tps rate

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group