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.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
