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@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.
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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
