Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 124479FA073
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 20 Oct 2007 17:11:13 -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 31946-01 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Sat, 20 Oct 2007 17:11:05 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from web56412.mail.re3.yahoo.com (web56412.mail.re3.yahoo.com
	[216.252.111.91])
	by postgresql.org (Postfix) with SMTP id 428D79F9DEC
	for <pgsql-performance@postgresql.org>;
	Sat, 20 Oct 2007 17:11:09 -0300 (ADT)
Received: (qmail 45901 invoked by uid 60001); 20 Oct 2007 20:11:00 -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=tcB10lXQWM5ce++UigpVuQP2Ek4hxknpS9Pywc3y5DibNAyIn/6k/i1QUs0ygUZZ2gXshLNsPp//JWAVPsW8wGPhBxFWYj1W1hNP2YVFGSqylMlPBUiV4QDZ6k3gw5ut6ns4zHM7MwUzi+Zf8oEvpmAsqTUygDmcEJfT71NJnAU=;
X-YMail-OSG: 
 uyx6AJEVM1mzcaltf1bQsdPa_BiA6fvFDj93uJGsYrCo5UQ9Hx.bW7RVDXtH7t6ds9IChr4o2aJa3LQtxa0H8tEBPLg1ssr_hrZiRFxcvK34zwXS4t1PCJxcJaRENPnz8ZK5evYjF2XGJrM-
Received: from [194.58.241.106] by web56412.mail.re3.yahoo.com via HTTP;
	Sat, 20 Oct 2007 13:11:00 PDT
X-Mailer: YahooMailRC/814.06 YahooMailWebService/0.7.134.12
Date: Sat, 20 Oct 2007 13:11:00 -0700 (PDT)
From: Pavel Velikhov <pvelikhov@yahoo.com>
Subject: Re: need help with a query
To: Kenneth Marshall <ktm@rice.edu>
Cc: "Jonah H. Harris" <jonah.harris@gmail.com>,
	pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-451641323-1192911060=:45761"
Message-ID: <639622.45761.qm@web56412.mail.re3.yahoo.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200710/237
X-Sequence-Number: 27259

--0-451641323-1192911060=:45761
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

Thanks a lot folks,=0A=0ALeft the query running for 10+ hours and had to ki=
ll it. I guess there really was no need to have lots of=0Ashared buffers (t=
he hope was that postgresql will cache the whole table). I ended up doing t=
his step inside=0Athe application as a pre-processing step. Can't have post=
gres running with different fsych options since this=0Awill be part of an "=
easy to install and run" app, that should just require a typical PosgreSQL =
installation.=0A=0APavel Velikhov=0A=0A----- Original Message ----=0AFrom: =
Kenneth Marshall <ktm@rice.edu>=0ATo: Pavel Velikhov <pvelikhov@yahoo.com>=
=0ACc: Jonah H. Harris <jonah.harris@gmail.com>; pgsql-performance@postgres=
ql.org=0ASent: Friday, October 19, 2007 8:17:48 PM=0ASubject: Re: [PERFORM]=
 need help with a query=0A=0AOn Fri, Oct 19, 2007 at 09:11:36AM -0700, Pave=
l Velikhov wrote:=0A> Thanks for you help!=0A> =0A> Got a very different qu=
ery plan this time, with a hash join between=0A links and articles. At leas=
t now postgres is using both shared memory=0A buffers and working mem, but =
its still completely IO bound, only getting=0A in 5-6% CPU once in a while.=
 I guess I can't squeeze more out of the=0A laptop, but I also have a machi=
ne with 16GB RAM that I'll try this on=0A next. Should I allocate tons of m=
emory into shared buffers or into the=0A working memory?=0A=0AThis is an ex=
tremely I/O intensive query that must rewrite every=0Aentry in the table. Y=
ou could speed it up by starting postgresql=0Awith fsync disabled, run the =
update, and then restart it with=0Afsync re-enabled.=0A=0AKen=0A=0A=0A=0A=
=0A=0A__________________________________________________=0ADo You Yahoo!?=
=0ATired of spam?  Yahoo! Mail has the best spam protection around =0Ahttp:=
//mail.yahoo.com 
--0-451641323-1192911060=:45761
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 style=3D"font-family: times new roman,new york,times,s=
erif; font-size: 12pt;">Thanks a lot folks,<br><br>Left the query running f=
or 10+ hours and had to kill it. I guess there really was no need to have l=
ots of<br>shared buffers (the hope was that postgresql will cache the whole=
 table). I ended up doing this step inside<br>the application as a pre-proc=
essing step. Can't have postgres running with different fsych options since=
 this<br>will be part of an "easy to install and run" app, that should just=
 require a typical PosgreSQL installation.<br><br>Pavel Velikhov<br><br><di=
v style=3D"font-family: times new roman,new york,times,serif; font-size: 12=
pt;">----- Original Message ----<br>From: Kenneth Marshall &lt;ktm@rice.edu=
&gt;<br>To: Pavel Velikhov &lt;pvelikhov@yahoo.com&gt;<br>Cc: Jonah H.
 Harris &lt;jonah.harris@gmail.com&gt;; pgsql-performance@postgresql.org<br=
>Sent: Friday, October 19, 2007 8:17:48 PM<br>Subject: Re: [PERFORM] need h=
elp with a query<br><br>On Fri, Oct 19, 2007 at 09:11:36AM -0700, Pavel Vel=
ikhov wrote:<br>&gt; Thanks for you help!<br>&gt; <br>&gt; Got a very diffe=
rent query plan this time, with a hash join between=0A links and articles. =
At least now postgres is using both shared memory=0A buffers and working me=
m, but its still completely IO bound, only getting=0A in 5-6% CPU once in a=
 while. I guess I can't squeeze more out of the=0A laptop, but I also have =
a machine with 16GB RAM that I'll try this on=0A next. Should I allocate to=
ns of memory into shared buffers or into the=0A working memory?<br><br>This=
 is an extremely I/O intensive query that must rewrite every<br>entry in th=
e table. You could speed it up by starting postgresql<br>with fsync disable=
d, run the update, and then restart it with<br>fsync re-enabled.<br><br>Ken=
<br></div><br></div></div><br>_____________________________________________=
_____<br>Do You Yahoo!?<br>Tired of spam?  Yahoo! Mail has the best spam pr=
otection around <br>http://mail.yahoo.com </body></html>
--0-451641323-1192911060=:45761--