Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 7CA269FB3DF for ; Tue, 31 Oct 2006 16:45:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 22626-05 for ; Tue, 31 Oct 2006 16:45:18 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.206]) by postgresql.org (Postfix) with ESMTP id C23049FB3C9 for ; Tue, 31 Oct 2006 16:45:17 -0400 (AST) Received: by nz-out-0102.google.com with SMTP id i11so1692446nzi for ; Tue, 31 Oct 2006 12:45:16 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=YoDjsfxJPBWfvj/RI9lIpcABldlUl4nNjxZrK7J7SI5uTSHMoOV7u5jjcbUYQlXRjBTX+WpwZy2aWVfmnrDWU5OD3X8BSSd6JJ3Uj1xr6REobSCnLvG2/CnGnrfinsHGYEJUGw5elp+0Pv4akS+lf2nhKbxhpaIOXWaXh8t5aXQ= Received: by 10.35.19.9 with SMTP id w9mr6084543pyi; Tue, 31 Oct 2006 12:45:16 -0800 (PST) Received: by 10.35.49.10 with HTTP; Tue, 31 Oct 2006 12:45:16 -0800 (PST) Message-ID: Date: Tue, 31 Oct 2006 16:45:16 -0400 From: "Worky Workerson" To: "Luke Lonergan" Subject: Re: Best COPY Performance Cc: pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/630 X-Sequence-Number: 21759 > Maybe it is just the PK *build* that slows it down, but I just tried some > small scale experiments on my MacBook Pro laptop (which has the same disk > performance as your server) and I get only a 10-15% slowdown from having a > PK on an integer column. The 10-15% slowdown was on 8.1.5 MPP, so it used > both CPUs to build the index and load at about 15 MB/s. ...snip... > What is your schema for the table? A single IP4 PK and 21 VARCHARs. It takes about 340 seconds to load a 1.9GB file with the PK index, and about 230 seconds without it (ALTER TABLE mytable DROP CONSTRAINT mytable_pkey), which is a pretty significant (~30%) savings. If I read the vmstat output correctly (i.e. the cpu us column), I'm still at 12% and thus still cpu-bound, except for when the checkpoint occurs, i.e (everything is chugging along similar to the first line, then stuff gets wonky): r b swpd free buff cache si so bi bo in cs us sy id wa 2 0 279028 4620040 717940 9697664 0 0 0 19735 1242 7534 13 4 82 1 1 2 279028 4476120 718120 9776840 0 0 0 2225483 1354 5269 13 6 71 11 0 3 279028 4412928 718320 9866672 0 0 2 19746 1324 3978 10 2 69 18 1 1 279028 4334112 718528 9971456 0 0 0 20615 1311 5912 10 3 69 18 0 1 279028 4279904 718608 9995244 0 0 0 134946 1205 674 1 3 85 11 0 2 279028 4307344 718616 9995304 0 0 0 54 1132 247 0 1 77 22 1 0 279028 7411104 718768 6933860 0 0 0 9942 1148 3618 11 6 80 3 1 0 279028 7329312 718964 7015536 0 0 1 19766 1232 5108 13 2 84 1 Also, as a semi-side note, I only have a single checkpoint without the index, while I have 2 with the index.