From: M. D. <lists@turnkey.bz>
To: pgsql-performance@postgresql.org
Subject: Re: hardware advice
Date: Thu, 27 Sep 2012 15:22:41 -0600
Message-ID: <5064C3A1.7070805@turnkey.bz> (raw)
In-Reply-To: <CAOR=d=02nGu3fMppkVZWeiDYOpWrXi1PT2ZSxx-fZ+Uxp2WuOw@mail.gmail.com>
References: <5064A4DC.5090304@turnkey.bz>
<CAFwQ8rf4Cnt9Ksd+3GbkkWtZ3NM+hYukd1gKRLmsihXnKzVteA@mail.gmail.com>
<5064BB1E.4090904@turnkey.bz>
<CAOR=d=02nGu3fMppkVZWeiDYOpWrXi1PT2ZSxx-fZ+Uxp2WuOw@mail.gmail.com>
On 09/27/2012 02:55 PM, Scott Marlowe wrote:
> On Thu, Sep 27, 2012 at 2:46 PM, M. D. <lists@turnkey.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.
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@turnkey.bz
Subject: Re: hardware advice
In-Reply-To: <5064C3A1.7070805@turnkey.bz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox