Hi All,
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?
Thanks!
Chris
-----------------------------------------
IMPORTANT: The sender intends that this electronic message is for
exclusive use by the person to whom it is addressed. This message
may contain information that is confidential or privileged and
exempt from disclosure under applicable law. If the reader of this
message is not an intended recipient, be aware that any disclosure,
dissemination, distribution or copying of this communication, or
the use of its contents, is prohibited. If you have received this
message in error, please immediately notify the sender of your
inadvertent receipt and delete this message from all data storage
systems. Thank you.