Re: slow update of index during insert/copy

[Thomas Finneid <tfinneid@xxxxxxxxxxxxxxxxxxxxx>]

Scott Carey wrote:
> Are you even getting COPY to work with JDBC?  As far as I am aware, COPY 
> doesn't work with JDBC at the moment:

I used a patched jdbc driver, provided by someone on the list, dont have 
the reference at hand. It works perfectly and its about 5 times faster, 
for my job, than insert.

> As suggested, you should determine if you are disk bound or CPU bound.  
> My experience with COPY is that it is suprisingly easy to make it CPU 
> bound, but the conditions for that can vary quire a bit from schema to 
> schema and hardware to hardware.

COPY is not the problem, as far as I see. The problem is the update 
speed of the index. I tested the same procedure on a table with and 
without an index. Having an index makes it 200-250% slower, than without.

But as you state I should check whether the problem is cpu or disk 
bound. In addition, as someone else suggested, I might need to move the 
indexes to a different disk, which is not a bad idea considering the 
index becomes quite large with up 125K rows a second.

But I haver another consern, which is the db server configuration. I am 
not entirely convinced the db is configured prperly. I had one problem 
where the disk started thrashing after the table had reached a certainb 
size, so when I configured shmmax, and the corresponding in pg, properly 
I got rid of the trashing. I will have to read through the documentation 
 properly.

regards

thomas