Skip site navigation (1) Skip section navigation (2)

Identical query slower on 8.4 vs 8.3

From: Patrick Donlin <pdonlin(at)oaisd(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Identical query slower on 8.4 vs 8.3
Date: 2010-07-15 14:41:22
Message-ID: 1187615879.13268921279204882941.JavaMail.root@mm-mailstore02.merit.edu (view raw or flat)
Thread:
Lists: pgsql-performance
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 

Responses

pgsql-performance by date

Next:From: Thom BrownDate: 2010-07-15 14:50:29
Subject: Re: Identical query slower on 8.4 vs 8.3
Previous:From: Yeb HavingaDate: 2010-07-15 07:58:50
Subject: Re: Query optimization problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group