Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THXxW-0000fE-9r for pgsql-performance@postgresql.org; Fri, 28 Sep 2012 10:38:14 +0000 Received: from wizmail.org ([2a00:1940:107::2:0:0]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1THXxU-00080n-A5 for pgsql-performance@postgresql.org; Fri, 28 Sep 2012 10:38:13 +0000 Received: from [46.33.133.68] (helo=ebony.jgh.adsl.wizint.net) from_AS 51561 by wizmail.org with esmtpsa (TLSv1:DHE-RSA-CAMELLIA256-SHA:256) (Exim 4.77) id 1THXxQ-000414-PO (return-path ); Fri, 28 Sep 2012 10:38:09 +0000 Message-ID: <50657E0F.4050506@wizmail.org> Date: Fri, 28 Sep 2012 11:38:07 +0100 From: Jeremy Harris User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.21) Gecko/20090320 Fedora/2.0.0.21-1.fc9 Thunderbird/2.0.0.21 Mnenhy/0.7.5.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: hardware advice References: <5064A4DC.5090304@turnkey.bz> <5064BB1E.4090904@turnkey.bz> <5064C3A1.7070805@turnkey.bz> In-Reply-To: <5064C3A1.7070805@turnkey.bz> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.7 (--) X-Archive-Number: 201209/189 X-Sequence-Number: 47935 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. 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