Identical query slower on 8.4 vs 8.3

От: Patrick Donlin
Тема: Identical query slower on 8.4 vs 8.3
Дата: ,
Msg-id: 1187615879.13268921279204882941.JavaMail.root@mm-mailstore02.merit.edu
(см: обсуждение, исходный текст)
Ответы: Re: Identical query slower on 8.4 vs 8.3  (Thom Brown)
Re: Identical query slower on 8.4 vs 8.3  ("Kevin Grittner")
Re: Identical query slower on 8.4 vs 8.3  (Jon Nelson)
Re: Identical query slower on 8.4 vs 8.3  ("Joshua D. Drake")
Re: Identical query slower on 8.4 vs 8.3  ("Joshua D. Drake")
Список: pgsql-performance

Скрыть дерево обсуждения

Identical query slower on 8.4 vs 8.3  (Patrick Donlin, )
 Re: Identical query slower on 8.4 vs 8.3  (Thom Brown, )
 Re: Identical query slower on 8.4 vs 8.3  ("Kevin Grittner", )
  Re: Identical query slower on 8.4 vs 8.3  (Patrick Donlin, )
   Re: Identical query slower on 8.4 vs 8.3  (Alvaro Herrera, )
   Re: Identical query slower on 8.4 vs 8.3  (Andy Colson, )
   Re: Identical query slower on 8.4 vs 8.3  ("Kevin Grittner", )
   Re: Identical query slower on 8.4 vs 8.3  (Merlin Moncure, )
    Re: Identical query slower on 8.4 vs 8.3  (Patrick Donlin, )
   Re: Identical query slower on 8.4 vs 8.3  ("Igor Neyman", )
  Re: Identical query slower on 8.4 vs 8.3  (, )
 Re: Identical query slower on 8.4 vs 8.3  (Jon Nelson, )
 Re: Identical query slower on 8.4 vs 8.3  ("Joshua D. Drake", )
 Re: Identical query slower on 8.4 vs 8.3  ("Joshua D. Drake", )

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

В списке pgsql-performance по дате сообщения:

От: Patrick Donlin
Дата:
Сообщение: Re: Identical query slower on 8.4 vs 8.3
От: Scott Carey
Дата:
Сообщение: Re: performance on new linux box