X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 74907D1D077 for ; Fri, 23 Apr 2004 01:39:17 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 56323-04 for ; Fri, 23 Apr 2004 01:39:16 -0300 (ADT) Received: from joeconway.com (66-146-172-82.skyriver.net [66.146.172.82]) by svr1.postgresql.org (Postfix) with ESMTP id A384DD1CCCA for ; Fri, 23 Apr 2004 01:39:15 -0300 (ADT) Received: from [192.168.4.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 1519993; Thu, 22 Apr 2004 21:33:27 -0700 Message-ID: <40889DAD.6080703@joeconway.com> Date: Thu, 22 Apr 2004 21:38:05 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Sean Shanny Cc: pgsql-performance@postgresql.org Subject: Re: Looking for ideas on how to speed up warehouse loading References: <40883FAB.3070109@earthlink.net> In-Reply-To: <40883FAB.3070109@earthlink.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200404/401 X-Sequence-Number: 6701 Sean Shanny wrote: > explain analyze SELECT t1.id, t2.url FROM referral_temp t2 LEFT OUTER > JOIN d_referral t1 ON t2.url = t1.referral_raw_url ORDER BY t1.id; > What I would like to know is if there are better ways to do the join? I > need to get all the rows back from the referral_temp table as they are > used for assigning FK's for the fact table later in processing. When I > iterate over the values that I get back those with t1.id = null I assign > a new FK and push both into the d_referral table as new entries as well > as a text file for later use. The matching records are written to a > text file for later use. Would something like this work any better (without disabling index scans): SELECT t1.id, t2.url FROM referral_temp t2, d_referral t1 WHERE t1.referral_raw_url = t2.url; SELECT t1.id, t2.url FROM referral_temp t2 WHERE NOT EXISTS (select 1 FROM d_referral t1 WHERE t1.referral_raw_url = t2.url); ? Joe