Skip site navigation (1) Skip section navigation (2)

Re: Looking for ideas on how to speed up warehouse loading

From: Joe Conway <mail(at)joeconway(dot)com>
To: Sean Shanny <shannyconsulting(at)earthlink(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Looking for ideas on how to speed up warehouse loading
Date: 2004-04-23 04:38:05
Message-ID: 40889DAD.6080703@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-performance
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;

<process rows with a match>

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);

<process rows without a match>

?

Joe

In response to

Responses

pgsql-performance by date

Next:From: Aaron WermanDate: 2004-04-23 12:19:36
Subject: Re: Looking for ideas on how to speed up warehouse loading
Previous:From: Anjan DaveDate: 2004-04-23 02:27:55
Subject: Re: Wierd context-switching issue on Xeon

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group