Re: strange index performance?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux