Porell, Chris wrote:
I have recently migrated a Postgres database from 7.4 running on gentoo to 8.1 running on SLES 10. I migrated the data using pg_dump and then running the SQL in psql. The old server was a dual AMD opteron 2.6 GHz machine with a RAID 5 array and 4GB memory. The new machine is a dual dual-core AMD Opteron 2.6GHz with RAID 1 and 16GB memory. Several reports are taking way too long to run. I snagged a SELECT from one of the reports. It is a fairly complex query with 4 joins, which unfortunately I can't share. I can say that the plan for the query on both machines looks nearly identical - that is there are no sequential scans happening on the old DB server that aren't also happening on the new server. The total database size is less that 1GB. On my new DB server, it takes about 27 seconds for this query to run. On the old server, it takes 2 seconds. I've changed shared_buffers, checkpoint_segments, effective_cache_size and random_page_cost in an attempt to improve performance. That has helped a little, but 27 seconds is still almost 14 times what it used to take. I feel I'm missing something obvious, and I don't have much experience with Postgres. Any ideas?
I assume you have analyzed after you reloaded the data? If so, can you get a explain analzye of the query in question from both the old server and the new server and post that to the list.
