Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
	by mail.postgresql.org (Postfix) with ESMTP id 01A6E63318A
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 15 Jul 2010 11:48:23 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia,
	port 10024) with ESMTP id 53596-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu, 15 Jul 2010 14:48:14 +0000 (UTC)
X-Greylist: delayed 00:06:42.754822 by SQLgrey-1.7.6
Received: from mm-mailstore02.merit.edu (mm-mailstore02.merit.edu
	[207.74.184.19])
	by mail.postgresql.org (Postfix) with ESMTP id AA924633140
	for <pgsql-performance@postgresql.org>;
	Thu, 15 Jul 2010 11:48:15 -0300 (ADT)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by mm-mailstore02.merit.edu (Postfix) with ESMTP id 010FB6800FF6
	for <pgsql-performance@postgresql.org>;
	Thu, 15 Jul 2010 10:41:28 -0400 (EDT)
X-Virus-Scanned: amavisd-new at mm-mailstore02.merit.edu
Received: from mm-mailstore02.merit.edu ([127.0.0.1])
	by localhost (mm-mailstore02.merit.edu [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id TcUMeyKeBs04 for <pgsql-performance@postgresql.org>;
	Thu, 15 Jul 2010 10:41:23 -0400 (EDT)
Received: from mm-mailstore02.merit.edu (mm-mailstore02.merit.edu
	[10.108.2.25])
	by mm-mailstore02.merit.edu (Postfix) with ESMTP id 009936800FF8
	for <pgsql-performance@postgresql.org>;
	Thu, 15 Jul 2010 10:41:22 -0400 (EDT)
Date: Thu, 15 Jul 2010 10:41:22 -0400 (EDT)
From: Patrick Donlin <pdonlin@oaisd.org>
To: pgsql-performance@postgresql.org
Message-ID: 
 <1187615879.13268921279204882941.JavaMail.root@mm-mailstore02.merit.edu>
In-Reply-To: 
 <282748664.13267761279204611150.JavaMail.root@mm-mailstore02.merit.edu>
Subject: Identical query slower on 8.4 vs 8.3
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_447276_638425413.1279204882940"
X-Originating-IP: [64.9.158.13]
X-Mailer: Zimbra 5.0.21_GA_3150.RHEL5_64 (ZimbraWebClient - FF3.0
	(Mac)/5.0.21_GA_3150.RHEL5_64)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-4.199 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_MED=-2.3
X-Spam-Level: 
X-Archive-Number: 201007/234
X-Sequence-Number: 39623

------=_Part_447276_638425413.1279204882940
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

I have two servers with equal specs, one of them running 8.3.7 and the new server running 8.4.4. The only tweak I have made from the default install (from Ubuntu repositories) is increasing shared_buffers to 768MB. Both servers are running 64-bit, but are different releases of Ubuntu. 

This is the query I am running: 

SELECT DISTINCT test.tid, testresult.trscore, testresult.trpossiblescore, testresult.trstart, 
testresult.trfinish, testresult.trscorebreakdown, testresult.fk_sid, testresult.fk_tid, test.tname, 
qr.qrscore, qr.qrtotalscore, testresult.trid, qr.qrid 
FROM testresult, test, questionresult qr 
WHERE test.tid = testresult.fk_tid AND qr.fk_trid = testresult.trid 
ORDER BY test.tid; 

Results when running on the v8.3.7 server.... 
Total query runtime: 32185 ms. 
700536 rows retrieved. 

Results when running on the v8.4.4 server.... 
Total query runtime: 164227 ms. 
700536 rows retrieved. 

Results when running on a different v8.4.4 server with slightly faster hardware and shared_buffers at 1024MB.... 
(this one has a few more rows of data due to this being the server that is currently live, so it has more recent data) 
Total query runtime: 157931 ms. 
700556 rows retrieved. 


Anyone have any ideas on where I should start looking to figure this out? I didn't perform any special steps when moving to v8.4, I just did a pg_dump from the 8.3 server and restored it on the new 8.4 servers. Maybe that is where I made a mistake. 

Thanks! 
Patrick 

------=_Part_447276_638425413.1279204882940
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><style type=3D'text/css'>p { margin: 0; }</style></head><body><=
div style=3D'font-family: Arial; font-size: 12pt; color: #000000'>I have tw=
o servers with equal specs, one of them running 8.3.7 and the new server ru=
nning 8.4.4. The only tweak I have made from the default install (from Ubun=
tu repositories) is increasing shared_buffers to 768MB. Both servers are ru=
nning 64-bit, but are different releases of Ubuntu.<br><br>This is the quer=
y I am running:<br><br>SELECT DISTINCT test.tid, testresult.trscore, testre=
sult.trpossiblescore, testresult.trstart,<br>testresult.trfinish, testresul=
t.trscorebreakdown, testresult.fk_sid, testresult.fk_tid, test.tname,<br>qr=
.qrscore, qr.qrtotalscore, testresult.trid, qr.qrid<br>FROM testresult, tes=
t, questionresult qr<br>WHERE test.tid =3D testresult.fk_tid AND qr.fk_trid=
 =3D testresult.trid<br>ORDER BY test.tid;<br><br>Results when running on t=
he v8.3.7 server....<br>Total query runtime: 32185 ms.<br>700536 rows retri=
eved.<br><br>Results when running on the v8.4.4 server....<br>Total query r=
untime: 164227 ms.<br>700536 rows retrieved.<br><br>Results when running on=
 a different v8.4.4 server with slightly faster hardware and shared_buffers=
 at 1024MB....<br>(this one has a few more rows of data due to this being t=
he server that is currently live, so it has more recent data)<br>Total quer=
y runtime: 157931 ms.<br>700556 rows retrieved.<br><br><br>Anyone have any =
ideas on where I should start looking to figure this out? I didn't perform =
any special steps when moving to v8.4, I just did a pg_dump from the 8.3 se=
rver and restored it on the new 8.4 servers. Maybe that is where I made a m=
istake.<br><br>Thanks!<br>Patrick<br></div></body></html>
------=_Part_447276_638425413.1279204882940--