Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 56A369FB33F
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 19 Oct 2007 12:05:23 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 82534-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 19 Oct 2007 12:04:59 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from web56406.mail.re3.yahoo.com (web56406.mail.re3.yahoo.com
	[216.252.111.85])
	by postgresql.org (Postfix) with SMTP id 4D2549FA173
	for <pgsql-performance@postgresql.org>;
	Fri, 19 Oct 2007 12:05:17 -0300 (ADT)
Received: (qmail 41060 invoked by uid 60001); 19 Oct 2007 15:05:15 -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:MIME-Version:Content-Type:Message-ID;
	b=bfmMR6sWg+wKXUhjdzbgY4f6TXh0t3cIT1U6PAVzKmSDsiOxj61TxKIpFKL5HBmDtmUcXcSyHQXMBqDQ5hVVUwFzGAfik22qlAGgvuie/x/7MElqrIqzrjt0qYmEUjutt7rh7bkhwq2ODuKpNBriuTAm/vDPZyJsBIfCLoYbjcM=;
X-YMail-OSG: 
 CYS8FSgVM1m76GX716x8M.XADtadwQQs1laqIcMcaHJyG81DwYULRHUeUsW.yQlRyJ84QX4t3fDllccPAXKC89YVTA--
Received: from [83.149.198.98] by web56406.mail.re3.yahoo.com via HTTP;
	Fri, 19 Oct 2007 08:05:15 PDT
X-Mailer: YahooMailRC/814.06 YahooMailWebService/0.7.134.12
Date: Fri, 19 Oct 2007 08:05:15 -0700 (PDT)
From: Pavel Velikhov <pvelikhov@yahoo.com>
Subject: need help with a query
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-994181195-1192806315=:37701"
Message-ID: <878599.37701.qm@web56406.mail.re3.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200710/230
X-Sequence-Number: 27252

--0-994181195-1192806315=:37701
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

Hi,=0A=0A  I am updating a big table (90M records) with data from another r=
ather large table (4M entries). Here is my update query:=0A=0A    update li=
nks set target_size =3D =0A    ( select size from articles where articles.a=
rticle_id =3D links.article_to)=0A=0A I have built all the indexes one migh=
t need, increased shared mem buffers to 400MB, I looked at the query plan a=
nd it looks reasonable.=0ABut its taking an eternity to run: I've been runn=
ing the query for 3 hours now on my new Mac laptop, and looking at the acti=
vity monitor I see that postrges is spending all of this time in disk IO (a=
verage CPU load of postgres process is about 4-5%).=0A=0A However, just loo=
king at the query, postgres could cache the articles table and do a single =
pass over the links table...=0A=0A Please let me know if there is a good so=
lution to this.=0A=0AThanks!=0APavel Velikhov=0AInstitute of Systems Progra=
mming=0ARussian Academy of Sciences=0A=0A=0A=0A____________________________=
______________________=0ADo You Yahoo!?=0ATired of spam?  Yahoo! Mail has t=
he best spam protection around =0Ahttp://mail.yahoo.com 
--0-994181195-1192806315=:37701
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D"text/css"><!-- DIV {margin:0px;} --></style></he=
ad><body><div style=3D"font-family:times new roman, new york, times, serif;=
font-size:12pt"><div>Hi,<br><br>&nbsp; I am updating a big table (90M recor=
ds) with data from another rather large table (4M entries). Here is my upda=
te query:<br><br>&nbsp;&nbsp;&nbsp; update links set target_size =3D <br>&n=
bsp;&nbsp;&nbsp; ( select size from articles where articles.article_id =3D =
links.article_to)<br><br>&nbsp;I have built all the indexes one might need,=
 increased shared mem buffers to 400MB, I looked at the query plan and it l=
ooks reasonable.<br>But its taking an eternity to run: I've been running th=
e query for 3 hours now on my new Mac laptop, and looking at the activity m=
onitor I see that postrges is spending all of this time in disk IO (average=
 CPU load of postgres process is about 4-5%).<br><br>&nbsp;However, just lo=
oking at the query, postgres could cache the articles table and do a single
 pass over the links table...<br><br>&nbsp;Please let me know if there is a=
 good solution to this.<br><br>Thanks!<br>Pavel Velikhov<br>Institute of Sy=
stems Programming<br>Russian Academy of Sciences<br></div></div><br>_______=
___________________________________________<br>Do You Yahoo!?<br>Tired of s=
pam?  Yahoo! Mail has the best spam protection around <br>http://mail.yahoo=
.com </body></html>
--0-994181195-1192806315=:37701--