Date: Sun, 29 Jan 2006 19:32:15 -0500
From: Michael Adler <adler@pobox.com>
To: Mike Biamonte <mike@dbeat.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Huge Data sets, simple queries
Message-ID: <20060130003215.GA20269@pobox.com>
References: <007101c623a9$82207c10$0200a8c0@videobox>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <007101c623a9$82207c10$0200a8c0@videobox>
User-Agent: Mutt/1.5.10i

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