Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THKyd-0001xD-DI for pgsql-performance@postgresql.org; Thu, 27 Sep 2012 20:46:31 +0000 Received: from dime159.dizinc.com ([66.7.216.77]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THKyb-0006Kw-3j for pgsql-performance@postgresql.org; Thu, 27 Sep 2012 20:46:30 +0000 Received: from [200.32.232.82] (port=55880 helo=[10.12.0.55]) by dime159.dizinc.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.77) (envelope-from ) id 1THKyY-0000j1-6x; Thu, 27 Sep 2012 16:46:27 -0400 Message-ID: <5064BB1E.4090904@turnkey.bz> Date: Thu, 27 Sep 2012 14:46:22 -0600 From: "M. D." User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:15.0) Gecko/20120827 Thunderbird/15.0 MIME-Version: 1.0 To: Craig James CC: pgsql-performance@postgresql.org Subject: Re: hardware advice References: <5064A4DC.5090304@turnkey.bz> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - dime159.dizinc.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - turnkey.bz X-Source: X-Source-Args: X-Source-Dir: X-Pg-Spam-Score: -0.0 (/) X-Archive-Number: 201209/165 X-Sequence-Number: 47911 On 09/27/2012 01:37 PM, Craig James wrote: > I don't think you've supplied enough information for anyone to give > you a meaningful answer. What's your current configuration? Are you > I/O bound, CPU bound, memory limited, or some other problem? You need > to do a specific analysis of the queries that are causing you problems > (i.e. why do you need to upgrade at all?) My current configuration is a Dell PE 1900, E5335, 16GB Ram, 2 250GB Raid 0. I'm buying a new server mostly because the current one is a bit slow and I need a new gateway server, so to get faster database responses, I want to upgrade this and use the old one for gateway. The current system is limited to 16GB Ram, so it is basically maxed out. A query that takes 89 seconds right now is run on a regular basis (82,000 rows): select item.item_id,item_plu.number,item.description, (select number from account where asset_acct = account_id), (select number from account where expense_acct = account_id), (select number from account where income_acct = account_id), (select dept.name from dept where dept.dept_id = item.dept_id) as dept, (select subdept.name from subdept where subdept.subdept_id = item.subdept_id) as subdept, (select sum(on_hand) from item_change where item_change.item_id = item.item_id) as on_hand, (select sum(on_order) from item_change where item_change.item_id = item.item_id) as on_order, (select sum(total_cost) from item_change where item_change.item_id = item.item_id) as total_cost from item join item_plu on item.item_id = item_plu.item_id and item_plu.seq_num = 0 where item.inactive_on is null and exists (select item_num.number from item_num where item_num.item_id = item.item_id) and exists (select stocked from item_store where stocked = 'Y' and inactive_on is null and item_store.item_id = item.item_id) Explain analyse: http://explain.depesz.com/s/sGq