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@xxxxxxxxxx> wrote: > On 09/27/2012 02:55 PM, Scott Marlowe wrote: >> >> On Thu, Sep 27, 2012 at 2:46 PM, M. D. <lists@xxxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
