Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id BC78D9FB366 for ; Thu, 28 Sep 2006 17:15:15 -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 05637-05 for ; Thu, 28 Sep 2006 20:15:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 8D33E9FA5CE for ; Thu, 28 Sep 2006 17:15:08 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 3BECF30E2D; Thu, 28 Sep 2006 22:15:06 +0200 (MET DST) From: "Carlo Stonebanks" X-Newsgroups: pgsql.performance Subject: Re: Performace Optimization for Dummies Date: Thu, 28 Sep 2006 16:15:03 -0400 Organization: Hub.Org Networking Services Lines: 47 Message-ID: References: <20060928184538.GV34238@nasby.net> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-RFC2646: Format=Flowed; Original To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200609/404 X-Sequence-Number: 21090 The deduplication process requires so many programmed procedures that it runs on the client. Most of the de-dupe lookups are not "straight" lookups, but calculated ones emplying fuzzy logic. This is because we cannot dictate the format of our input data and must deduplicate with what we get. This was one of the reasons why I went with PostgreSQL in the first place, because of the server-side programming options. However, I saw incredible performance hits when running processes on the server and I partially abandoned the idea (some custom-buiilt name-comparison functions still run on the server). I am using Tcl on both the server and the client. I'm not a fan of Tcl, but it appears to be quite well implemented and feature-rich in PostgreSQL. I find PL/pgsql awkward - even compared to Tcl. (After all, I'm just a programmer... we do tend to be a little limited.) The import program actually runs on the server box as a db client and involves about 3000 lines of code (and it will certainly grow steadily as we add compatability with more import formats). Could a process involving that much logic run on the db server, and would there really be a benefit? Carlo ""Jim C. Nasby"" wrote in message news:20060928184538.GV34238@nasby.net... > On Thu, Sep 28, 2006 at 01:53:22PM -0400, Carlo Stonebanks wrote: >> > are you using the 'copy' interface? >> >> Straightforward inserts - the import data has to transformed, normalised >> and >> de-duped by the import program. I imagine the copy interface is for more >> straightforward data importing. These are - buy necessity - single row >> inserts. > > BTW, stuff like de-duping is something you really want the database - > not an external program - to be doing. Think about loading the data into > a temporary table and then working on it from there. > -- > Jim Nasby jim@nasby.net > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >