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

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

From: "Aaron Werman" <awerman2(at)hotmail(dot)com>
To: "Joe Conway" <mail(at)joeconway(dot)com>,"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 12:19:36
Message-ID: BAY18-DAV4HTtt0zb8v000019ad@hotmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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" <mail(at)joeconway(dot)com>
To: "Sean Shanny" <shannyconsulting(at)earthlink(dot)net>
Cc: <pgsql-performance(at)postgresql(dot)org>
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;
>
> <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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

In response to

pgsql-performance by date

Next:From: Chris HooverDate: 2004-04-23 13:31:17
Subject: Help with performance problems
Previous:From: Joe ConwayDate: 2004-04-23 04:38:05
Subject: Re: Looking for ideas on how to speed up warehouse loading

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