From: Jeremy Harris <jgh@wizmail.org>
To: pgsql-performance@postgresql.org
Subject: Re: hardware advice
Date: Fri, 28 Sep 2012 11:38:07 +0100
Message-ID: <50657E0F.4050506@wizmail.org> (raw)
In-Reply-To: <5064C3A1.7070805@turnkey.bz>
References: <5064A4DC.5090304@turnkey.bz>
<CAFwQ8rf4Cnt9Ksd+3GbkkWtZ3NM+hYukd1gKRLmsihXnKzVteA@mail.gmail.com>
<5064BB1E.4090904@turnkey.bz>
<CAOR=d=02nGu3fMppkVZWeiDYOpWrXi1PT2ZSxx-fZ+Uxp2WuOw@mail.gmail.com>
<5064C3A1.7070805@turnkey.bz>
On 09/27/2012 10:22 PM, M. D. wrote:
> 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.
Is there any prospect of the planner/executor being taught to
merge each of those groups of three index scans,
to aid this sort of poor query?
--
Jeremy
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: jgh@wizmail.org
Subject: Re: hardware advice
In-Reply-To: <50657E0F.4050506@wizmail.org>
* 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