Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id E14319FB79C for ; Tue, 23 Oct 2007 06:54:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 49544-03 for ; Tue, 23 Oct 2007 06:54:10 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from web56401.mail.re3.yahoo.com (web56401.mail.re3.yahoo.com [216.252.111.80]) by postgresql.org (Postfix) with SMTP id 43E799FB778 for ; Tue, 23 Oct 2007 06:54:10 -0300 (ADT) Received: (qmail 97828 invoked by uid 60001); 23 Oct 2007 09:54:05 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type:Message-ID; b=HLrYux3kSkXlILpnlU4nwVLpNAcmA1BGulaAj8ax/GgOKwHINRRjdvroBcWT7QAgmp8wmRcjZEZHxwWBpbbm3FyogClzQqPZ6cnDMlbi3PksKMUcvoxbZG9HlZp/G6iNrcT2IrcVhq7qIB+yDpf4wA0zpOfHITATOvSTubG+z24=; X-YMail-OSG: vBlZWbkVM1nycYZvH7lSTpqO05WDp.iurxL4To_rdkQ_Yk6riefEOGl6zdDNAYw9IALl_ybmBqLiDs1uRHouzK4dP_l_VOgtJAEPwVHpq6xGTa8VLpk- Received: from [83.149.198.98] by web56401.mail.re3.yahoo.com via HTTP; Tue, 23 Oct 2007 02:54:04 PDT X-Mailer: YahooMailRC/814.06 YahooMailWebService/0.7.134.12 Date: Tue, 23 Oct 2007 02:54:04 -0700 (PDT) From: Pavel Velikhov Subject: Re: need help with a query To: "Jonah H. Harris" Cc: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1456965848-1193133244=:97083" Message-ID: <226961.97083.qm@web56401.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/247 X-Sequence-Number: 27269 --0-1456965848-1193133244=:97083 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable =0AOn 10/20/07, Pavel Velikhov wrote:=0A> Left the qu= ery running for 10+ hours and had to kill it. I guess=0A there=0A> really w= as no need to have lots of=0A> shared buffers (the hope was that postgresql= will cache the whole=0A table). I=0A> ended up doing this step inside=0A> = the application as a pre-processing step. Can't have postgres running=0A wi= th=0A> different fsych options since this=0A> will be part of an "easy to i= nstall and run" app, that should just=0A require a=0A> typical PosgreSQL in= stallation.=0A=0A>Is the size always different? If not, you could limit th= e updates:=0A=0A>UPDATE links=0A> SET target_size =3D size=0A>FROM article= s=0A>WHERE articles.article_id =3D links.article_to=0A> AND links.ta= rget_size !=3D articles.size;=0A=0AAh, this sounds better for sure! But its= probably as good as the scan with an index-scan subquery I was getting bef= ore...=0A=0A>Since this is a huge operation, what about trying:=0A=0A>CREAT= E TABLE links_new AS SELECT l.col1, l.col2, a.size as=0A>target_size, l.col= 4, ... FROM links l, articles a WHERE a.article_id =3D=0A>l.article_to;=0A= =0A>Then truncate links, copy the data from links_new. Alternatively, you= =0A>could drop links, rename links_new to links, and recreate the=0A>constr= aints.=0A=0A>I guess the real question is application design. Why doesn't = this app=0A>store size at runtime instead of having to batch this huge upda= te?=0A=0AThis is a link analysis application, I need to materialize all the= sizes for target=0Aarticles in order to have the runtime part (vs. the loa= ding part) run efficiently. I.e.=0AI really want to avoid a join with the a= rticles table at runtime.=0A=0AI have solved the size problem by other mean= s (I compute it in my loader), but=0AI still have one query that needs to u= pdate a pretty large percentage of the links table...=0AI have previously u= sed mysql, and for some reason I didn't have a problem with queries=0Alike = this (on the other hand mysql was crashing when building an index on articl= e_to in the=0Alinks relation, so I had to work without a critical index)...= =0A=0AThank!=0APavel=0A=0A=0A-- =0AJonah H. Harris, Sr. Software Architect = | phone: 732.331.1324=0AEnterpriseDB Corporation | fax: 732.= 331.1301=0A499 Thornall Street, 2nd Floor | jonah.harris@enterpris= edb.com=0AEdison, NJ 08837 | http://www.enterprisedb= .com/=0A=0A---------------------------(end of=0A broadcast)----------------= -----------=0ATIP 2: Don't 'kill -9' the postmaster=0A=0A=0A=0A=0A=0A______= ____________________________________________=0ADo You Yahoo!?=0ATired of sp= am? Yahoo! Mail has the best spam protection around =0Ahttp://mail.yahoo.c= om --0-1456965848-1193133244=:97083 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: quoted-printable

On 10/20/07, Pavel Velikhov <pvel= ikhov@yahoo.com> wrote:
> Left the query running for 10+ hours= and had to kill it. I guess=0A there
> really was no need to have lo= ts of
> shared buffers (the hope was that postgresql will cache the w= hole=0A table). I
> ended up doing this step inside
> the appli= cation as a pre-processing step. Can't have postgres running=0A with
>= ; different fsych options since this
> will be part of an "easy to in= stall and run" app, that should just=0A require a
> typical PosgreSQL= installation.

>Is the size always different?  If not, you c= ould limit the updates:

>UPDATE links
>  SET target_si= ze =3D size
>FROM articles
>WHERE articles.article_id =3D links= .article_to
>        AND links.target_size !=3D a= rticles.size;

Ah, this sounds better for sure! But its probably as g= ood as the scan with an index-scan subquery I was getting before...

= >Since this is a huge operation, what about trying:

>CREATE TA= BLE links_new AS SELECT l.col1, l.col2, a.size as
>target_size, l.col= 4, ... FROM links l, articles a WHERE a.article_id =3D
>l.article_to;=

>Then truncate links, copy the data from links_new.  Altern= atively, you
>could drop links, rename links_new to links, and recrea= te the
>constraints.

>I guess the real question is applicat= ion design.  Why doesn't this app
>store size at runtime instead of having to batch this huge upda= te?

This is a link analysis application, I need to materialize all t= he sizes for target
articles in order to have the runtime part (vs. the = loading part) run efficiently. I.e.
I really want to avoid a join with t= he articles table at runtime.

I have solved the size problem by othe= r means (I compute it in my loader), but
I still have one query that nee= ds to update a pretty large percentage of the links table...
I have prev= iously used mysql, and for some reason I didn't have a problem with queries=
like this (on the other hand mysql was crashing when building an index = on article_to in the
links relation, so I had to work without a critical= index)...

Thank!
Pavel


--
Jonah H. Harris, Sr. So= ftware Architect | phone: 732.331.1324
EnterpriseDB Corporation  &n= bsp;             | fax: 732.331.1301
499 Thornall Street, 2nd Floor        =   | jonah.harris@enterprisedb.com
Edison= , NJ 08837                  &n= bsp;     | http://www.enterprisedb.com/

---------------------------(en= d of=0A broadcast)---------------------------
TIP 2: Don't 'kill -9' the= postmaster


__________________________________= ________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the b= est spam protection around
http://mail.yahoo.com --0-1456965848-1193133244=:97083--