From: Joe Conway <mail@joeconway.com>
To: Sean Shanny <shannyconsulting@earthlink.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Looking for ideas on how to speed up warehouse loading
Date: Thu, 22 Apr 2004 21:38:05 -0700
Message-ID: <40889DAD.6080703@joeconway.com> (raw)
In-Reply-To: <40883FAB.3070109@earthlink.net>
References: <40883FAB.3070109@earthlink.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mail@joeconway.com, shannyconsulting@earthlink.net
Subject: Re: Looking for ideas on how to speed up warehouse loading
In-Reply-To: <40889DAD.6080703@joeconway.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox