Re: Huge Data sets, simple queries

[Michael Adler <adler(at)pobox(dot)com>]

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