On Fri, Jan 27, 2006 at 08:23:55PM -0500, Mike Biamonte wrote:
> This query took 18 hours on PG 8.1 on a Dual Xeon, RHEL3, (2.4
> Kernel) with RAID-10 (15K drives) and 12 GB Ram. I was expecting it
> to take about 4 hours - based on some experience with a similar
> dataset on a different machine (RH9, PG7.3 Dual Xeon, 4GB RAM,
> Raid-5 10K drives)
>
> It's difficult to just "try" various ideas because each attempt
> takes a full day to test. Real experience is needed here!
It seems like you are changing multiple variables at the same time.
I think you need to first compare the query plans with EXPLAIN SELECT
to see if they are significantly different. Your upgrade from 7.3 to
8.1 may have resulted in a less optimal plan.
Second, you should monitor your IO performance during the query
execution and test it independent of postgres. Then compare the stats
between the two systems.
As a side note, if you have many disks and you are concerned about
bottlenecks on read operations, RAID 5 may perform better than
RAID 10.
-Mike