Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 251FC64FF96 for ; Mon, 1 Sep 2008 07:16:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 55882-01 for ; Mon, 1 Sep 2008 07:16:14 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-out1.uio.no (mail-out1.uio.no [129.240.10.57]) by postgresql.org (Postfix) with ESMTP id 7583F64FF4F for ; Mon, 1 Sep 2008 07:16:18 -0300 (ADT) Received: from mail-mx6.uio.no ([129.240.10.47]) by mail-out1.uio.no with esmtp (Exim 4.69) (envelope-from ) id 1Ka6SF-0002QR-Ou for pgsql-performance@postgresql.org; Mon, 01 Sep 2008 12:16:15 +0200 Received: from [89.8.38.8] by mail-mx6.uio.no with esmtpsa (TLSv1:AES256-SHA:256) user tfinneid (Exim 4.69) (envelope-from ) id 1Ka6SF-0005xU-2v for pgsql-performance@postgresql.org; Mon, 01 Sep 2008 12:16:15 +0200 Message-ID: <48BBCF07.8020709@ifi.uio.no> Date: Mon, 01 Sep 2008 12:16:23 +0100 From: Thomas Finneid User-Agent: Thunderbird 2.0.0.16 (Windows/20080708) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: slow update of index during insert/copy References: <48BA9D65.2020001@ifi.uio.no> <48BB4A2D.9080404@postnewspapers.com.au> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-UiO-SPF-Received: X-UiO-Spam-info: not spam, SpamAssassin (score=-5.0, required=5.0, autolearn=disabled, UIO_MAIL_IS_INTERNAL=-5, uiobl=NO, uiouri=NO) X-UiO-Scanned: C1710EA17D8590AD6E5F2681FA5B8918AF08B4AC X-UiO-SPAM-Test: remote_host: 89.8.38.8 spam_score: -49 maxlevel 200 minaction 2 bait 0 blacklist 0 greylist 0 ratelimit 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200809/2 X-Sequence-Number: 31315 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