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 58242D1C9E8 for ; Fri, 23 Apr 2004 09:19:35 -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 86772-07 for ; Fri, 23 Apr 2004 09:19:35 -0300 (ADT) Received: from hotmail.com (bay18-dav4.bay18.hotmail.com [65.54.187.184]) by svr1.postgresql.org (Postfix) with ESMTP id 79ACFD1C9B7 for ; Fri, 23 Apr 2004 09:19:31 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Fri, 23 Apr 2004 05:19:30 -0700 Received: from 67.81.102.201 by bay18-dav4.bay18.hotmail.com with DAV; Fri, 23 Apr 2004 12:19:30 +0000 X-Originating-IP: [67.81.102.201] X-Originating-Email: [awerman2@hotmail.com] X-Sender: awerman2@hotmail.com From: "Aaron Werman" To: "Joe Conway" , "Sean Shanny" Cc: References: <40883FAB.3070109@earthlink.net> <40889DAD.6080703@joeconway.com> Subject: Re: Looking for ideas on how to speed up warehouse loading Date: Fri, 23 Apr 2004 08:19:36 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Message-ID: X-OriginalArrivalTime: 23 Apr 2004 12:19:30.0871 (UTC) FILETIME=[3AB95C70:01C4292D] X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.2 tagged_above=0.0 required=5.0 tests=RCVD_IN_NJABL, RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200404/402 X-Sequence-Number: 6702 By definition, it is equivalent to: SELECT t1.id, t2.url FROM referral_temp t2 LEFT /*OUTER*/ JOIN d_referral t1 ON t2.url = t1.referral_raw_url union all SELECT null, url FROM referral_temp WHERE url is null ORDER BY 1; /Aaron ----- Original Message ----- From: "Joe Conway" To: "Sean Shanny" Cc: Sent: Friday, April 23, 2004 12:38 AM Subject: Re: [PERFORM] Looking for ideas on how to speed up warehouse loading > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >