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

Re: strange index performance?

From: Thomas Finneid <tfinneid(at)fcon(dot)no>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: strange index performance?
Date: 2009-01-26 09:10:13
Message-ID: 497D7DF5.9060305@fcon.no (view raw or flat)
Thread:
Lists: pgsql-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


In response to

Responses

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-01-26 12:09:55
Subject: Re: [PERFORMANCE] Buying hardware
Previous:From: Scott MarloweDate: 2009-01-25 22:12:04
Subject: Re: strange index performance?

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