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: Craig James <cjames(at)emolecules(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: hardware advice
Date: 2012-09-27 20:55:04
Message-ID: CAOR=d=02nGu3fMppkVZWeiDYOpWrXi1PT2ZSxx-fZ+Uxp2WuOw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.


In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2012-09-27 20:55:58
Subject: Re: hardware advice
Previous:From: Shaun ThomasDate: 2012-09-27 20:50:33
Subject: Re: hardware advice

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