Re: slow update of index during insert/copy

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