Skip site navigation (1) Skip section navigation (2)

Re: hardware advice

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: "M(dot) D(dot)" <lists(at)turnkey(dot)bz>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: hardware advice
Date: 2012-09-27 21:32:38
Message-ID: CAOR=d=0SBZSQNzrwtsxQZiLUJpLZ=He66GMb=tvG6ccfNJ19CA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Please don't take responses off list, someone else may have an insight I'd miss.

On Thu, Sep 27, 2012 at 3:20 PM, M. D. <lists(at)turnkey(dot)bz> wrote:
> On 09/27/2012 02:55 PM, Scott Marlowe wrote:
>>
>> On Thu, Sep 27, 2012 at 2:46 PM, M. D. <lists(at)turnkey(dot)bz> wrote:
>>>
>>> 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)
>>
>> Have you tried re-writing this query first?  Is there a reason to have
>> a bunch of subselects instead of joining the tables?  What pg version
>> are you running btw?  A newer version of pg might help too.
>>
> This query is inside an application (Quasar Accounting) written in Qt and I
> don't have access to the source code.  The query is cross database, so it's
> likely that's why it's written the way it is. The form this query is on also
> allows the user to add/remove columns, so it makes it a LOT easier from the
> application point of view to do columns as they are here.  I had at one
> point tried to make this same query a table join, but did not notice any
> performance difference in pg 8.x - been a while so don't remember exactly
> what version.

Have you tried cranking up work_mem and see if it helps this query at
least avoid a nested look on 80k rows?  If they'd fit in memory and
use bitmap hashes it should be MUCH faster than a nested loop.

>
> I'm currently on 9.0.  I will upgrade to 9.2 once I get a new server.  As
> noted above, I need to buy a new server anyway, so I'm going for this one
> and using the current as a VM server for several VMs and also a backup
> database server.

Well being on 9.0 should make a big diff from 8.2.  But again, without
enough work_mem for the query to use a bitmap hash or something more
efficient than a nested loop it's gonna be slow.


In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2012-09-27 21:36:35
Subject: Re: hardware advice
Previous:From: Evgeny ShishkinDate: 2012-09-27 21:29:48
Subject: Re: hardware advice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group