X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5FBC452835 for ; Thu, 23 Jun 2005 16:54:15 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 28780-05 for ; Thu, 23 Jun 2005 19:54:07 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.196]) by svr1.postgresql.org (Postfix) with ESMTP id B4A715280D for ; Thu, 23 Jun 2005 16:54:04 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 68so6114wra for ; Thu, 23 Jun 2005 12:54:06 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:references; b=WrI9dROzlHwgo6lqZzH9RSd/oRupnWlj171AfKxPB2EvWz/v9gS1TBJruoNxg6rW1i4IsCcbJ2MzTxS+zMIYBnuJk6DV7DbvBDY5cPV2qmDF/UQANrJHoCddIbFA2eVRuIBv/FNdPYE1o4dfcB88GMJMC5yyCfueV4UHM/QzvwA= Received: by 10.54.47.63 with SMTP id u63mr1350696wru; Thu, 23 Jun 2005 12:54:06 -0700 (PDT) Received: by 10.54.42.25 with HTTP; Thu, 23 Jun 2005 12:54:06 -0700 (PDT) Message-ID: Date: Thu, 23 Jun 2005 21:54:06 +0200 From: Meetesh Karia Reply-To: meetesh.karia@alumni.duke.edu To: Bricklen Anderson Subject: Re: ETL optimization Cc: pgsql-performance@postgresql.org In-Reply-To: <42BB0F9C.2050207@PresiNET.com> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3496_26142805.1119556446721" References: <42BB0F9C.2050207@PresiNET.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.53 tagged_above=0 required=5 tests=AWL, HTML_20_30, HTML_MESSAGE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200506/464 X-Sequence-Number: 13101 ------=_Part_3496_26142805.1119556446721 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I don't know what this will change wrt how often you need to run VACUUM (I'= m=20 a SQL Server guy), but instead of an update and insert, try a delete and=20 insert. You'll only have to find the duplicate rows once and your insert=20 doesn't need a where clause. Meetesh On 6/23/05, Bricklen Anderson wrote: >=20 > Situation: > I'm trying to optimize an ETL process with many upserts (~100k aggregated= =20 > rows) > (no duplicates allowed). The source (table t2) table holds around 14=20 > million > rows, and I'm grabbing them 100,000 rows at a time from t2, resulting in= =20 > about > 100,000 distinct rows in the destination table (t1). >=20 >=20 > What I've tried: >=20 > i. FOR EXECUTE LOOP over my result set (aggregated results, 100k-ish=20 > rows), and > try an update first, check the ROW_COUNT, if 0, then do an insert. > ... > run time: approx. 25 mins >=20 >=20 > ii. in a function (pseudo code), (table name is dynamic): > ... > up_stm :=3D > 'UPDATE '||t1||' SET x=3Dt2.x > FROM (select sum(x),a,b,c > from t2 > group by a,b,c) as t2 > WHERE '||t1||'.a=3Dt2.a AND '||t1||'.b=3Dt2.b AND '||t1||'.c=3Dt3.c'; >=20 > EXECUTE up_stm; >=20 > ins_stm :=3D > 'INSERT INTO '||t1||' (x,a,b,c) select x,a,b,c > FROM (select sum(x) as x,a,b,c from t2 group by a,b,c) as t2 > WHERE NOT EXISTS > (select true from '||t1||' > where '||t1||'.a=3Dt2.a > and '||t1||'.b=3Dt2.b > and '||t1||'.c=3Dt2.c > limit 1)'; >=20 > EXECUTE ins_stm; > ... >=20 > takes about 7 minutes. The performance of this is reasonable, but there i= s=20 > room > for improvement. > I think it's the NOT EXISTS subquery on the insert that makes the first= =20 > run > slow. Any revisions that may be faster (for the subquery)? > Note, this subquery is necessary so that duplicates don't get into the=20 > target > table (t1). >=20 > Subsequent runs will be mostly updates (and still slow), with few inserts= .=20 > I'm > not seeing a way for that update statement to be sped up, but maybe=20 > someone else > does? >=20 >=20 > iii. UNIQUE constraint on table "t1". This didn't seem to perform too=20 > badly with > fewer rows (preliminary tests), but as you'd expect, on error the whole > transaction would roll back. Is it possible to skip a row if it causes an= =20 > error, > as opposed to aborting the transaction altogether? >=20 >=20 >=20 > To summarize, I'm looking for the most efficient and fastest way to=20 > perform my > upserts. Tips and/or references to pertinent docs are also appreciated! > If any more information is necessary, please let me know. >=20 >=20 > (postgresql 8.0.3, linux) >=20 >=20 > Cheers, >=20 > Bricklen > -- > _______________________________ >=20 > This e-mail may be privileged and/or confidential, and the sender does > not waive any related rights and obligations. Any distribution, use or > copying of this e-mail or the information it contains by other than an > intended recipient is unauthorized. If you received this e-mail in > error, please advise me (by return e-mail or otherwise) immediately. > _______________________________ >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > ------=_Part_3496_26142805.1119556446721 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I don't know what this will change wrt how often you need to run VACUUM (I'm a SQL Server guy), but instead of an update and insert, try a delete and insert.  You'll only have to find the duplicate rows once and your insert doesn't need a where clause.

Meetesh

On 6/23/05, Bricklen Anderson <BAnderson@presinet.com> wrote:
Situation:
I'm trying to optimize an ETL process with many upserts (~100= k aggregated rows)
(no duplicates allowed). The source (table t2) table = holds around 14 million
rows, and I'm grabbing them 100,000 rows at a ti= me from t2, resulting in about
100,000 distinct rows in the destination table (t1).


What I'= ve tried:

i. FOR EXECUTE LOOP over my result set (aggregated results= , 100k-ish rows), and
try an update first, check the ROW_COUNT, if 0, th= en do an insert.
...
run time: approx. 25 mins


ii. in a function (pseudo c= ode), (table name is dynamic):
...
up_stm :=3D
'UPDATE '||t1||' SE= T x=3Dt2.x
FROM    (select sum(x),a,b,c
 &nb= sp;      from t2
    &= nbsp;   group by a,b,c) as t2
WHERE '||t1||'.a=3Dt2.a AND '||t1||'.b=3Dt2.b AND '||t1||'.c=3Dt3.c';
EXECUTE up_stm;

ins_stm :=3D
'INSERT INTO '||t1||' (x,a,b,c= ) select x,a,b,c
FROM (select sum(x) as x,a,b,c from t2 group by a,b,c) = as t2
WHERE NOT EXISTS
        (se= lect true from '||t1||'
        = where '||t1||'.a=3Dt2.a
        = and '||t1||'.b=3Dt2.b
        an= d '||t1||'.c=3Dt2.c
        limi= t 1)';

EXECUTE ins_stm;
...

takes about 7 minutes. The per= formance of this is reasonable, but there is room
for improvement.
I think it's the NOT EXISTS subquery on the insert = that makes the first run
slow. Any revisions that may be faster (for the= subquery)?
Note, this subquery is necessary so that duplicates don't ge= t into the target
table (t1).

Subsequent runs will be mostly updates (and still sl= ow), with few inserts. I'm
not seeing a way for that update statement to= be sped up, but maybe someone else
does?


iii. UNIQUE constra= int on table "t1". This didn't seem to perform too badly with
fewer rows (preliminary tests), but as you'd expect, on error the whole=
transaction would roll back. Is it possible to skip a row if it causes = an error,
as opposed to aborting the transaction altogether?



To summarize, I'm looking for the most efficient and fastest way to= perform my
upserts. Tips and/or references to pertinent docs are also a= ppreciated!
If any more information is necessary, please let me know.


(postgresql 8.0.3, linux)


Cheers,

Bricklen--
_______________________________

This e-mail may be privileged= and/or confidential, and the sender does
not waive any related rights a= nd obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an<= br>intended recipient is unauthorized. If you received this e-mail in
er= ror, please advise me (by return e-mail or otherwise) immediately.
_____= __________________________

---------------------------(end of broadcast)----------------------= -----
TIP 2: you can get off all lists at once with the unregister comma= nd
    (send "unregister YourEmailAddressHere&q= uot; to majordomo@postgresql.org)

------=_Part_3496_26142805.1119556446721--