Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 9852A9FA65C for ; Tue, 24 Oct 2006 21:33:53 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 16765-07 for ; Wed, 25 Oct 2006 00:33:38 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nz-out-0102.google.com (nz-out-0102.google.com [64.233.162.207]) by postgresql.org (Postfix) with ESMTP id 05F3D9FA5D8 for ; Tue, 24 Oct 2006 21:33:37 -0300 (ADT) Received: by nz-out-0102.google.com with SMTP id i11so860795nzi for ; Tue, 24 Oct 2006 17:33:36 -0700 (PDT) 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=mELJkwXT/2Gz9fOZGPo0eYsFS57hwH0qt41JQhW4/SF7MdGNjf0+QpfMmneuk41GnhsgGPbe6X0hKPjQ5wtVcBJEVf2vR8LUzJIrBrTSz243oLTIMXEyeor/0edeWCoxW8dBzyO358m06jhojwcKjWoXjOFSwo3BEBuxhRkxK1A= Received: by 10.35.82.15 with SMTP id j15mr8818304pyl; Tue, 24 Oct 2006 06:17:08 -0700 (PDT) Received: by 10.35.53.3 with HTTP; Tue, 24 Oct 2006 06:17:08 -0700 (PDT) Message-ID: Date: Tue, 24 Oct 2006 09:17:08 -0400 From: "Worky Workerson" To: "Jim C. Nasby" Subject: Re: Best COPY Performance Cc: "Merlin Moncure" , pgsql-performance@postgresql.org In-Reply-To: <20061023215913.GW26892@nasby.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20061023215913.GW26892@nasby.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.199 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/508 X-Sequence-Number: 21637 > http://stats.distributed.net used to use a perl script to do some > transformations before loading data into the database. IIRC, when we > switched to using C we saw 100x improvement in speed, so I suspect that > if you want performance perl isn't the way to go. I think you can > compile perl into C, so maybe that would help some. Like Craig mentioned, I have never seen those sorts of improvements going from perl->C, and developer efficiency is primo for me. I've profiled most of the stuff, and have used XS modules and Inline::C on the appropriate, often used functions, but I still think that it comes down to my using CSV and Text::CSV_XS. Even though its XS, CSV is still a pain in the ass. > Ultimately, you might be best of using triggers instead of rules for the > partitioning since then you could use copy. Or go to raw insert commands > that are wrapped in a transaction. Eh, I've put the partition loading logic in the loader, which seems to work out pretty well, especially since I keep things sorted and am the only one inserting into the DB and do so with bulk loads. But I'll keep this in mind for later use. Thanks!