Re: Identical query slower on 8.4 vs 8.3

On 15 July 2010 15:41, Patrick Donlin <pdonlin(at)oaisd(dot)org> wrote:
> 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
>

First thing to check is did you do a VACUUM ANALYZE on the database?

Thom