Re: Forcing order of Joins etc

From: Steve T <steve(at)retsol(dot)co(dot)uk>
To: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Forcing order of Joins etc
Date: 2008-10-03 09:59:00
Message-ID: 1223027940.3598.35.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

This has now created a follow - up question.
After changing the join_collapse_limit, I checked other parts of the
application to see what effect it had. It did then have an adverse
effect elsewhere. So I stopped the server, changed the parameter back
(commented out the line) and restarted the server. But I'm still getting
the improved response on the query below (although the adverse effect on
part of the system has gone again). Is there anyway I can see whet
parameters the server is actually running with?

On Fri, 2008-10-03 at 10:01 +0100, Steve T wrote:

> PostgreSQL 8.1.10
>
> Is there a way to force join order in a query?
> I have read the 'optimiser' section and achieved a vast improvement in
> speed by changing the 'join_collapse_limit' to 1. But the query is
> selectable by the user and I can't then change this configuratiion
> parameter on the fly.
> Is there a way of bracketing the joins to force the order? I did try
> to bracket the joins, but got syntax errors.
>
> (The change in the config parameter drops the query down from 5 Mins
> to <1Min - what I'm trying to do is to find the invoice headers for a
> date range (where clause) and to then expand out the invoice detail to
> the products involved).
> -----------------------------------------------------------------------------------------------------------------------------
> SELECT
> invH.invoiceNo AS Inv_No,
> srcD.recNo AS Claim_No,
> srcE.externalReference AS Claim_Ref,
> invH.invoiceDate AS Invoice_Date,
> COALESCE(cust.title,'')||'_'||COALESCE(cust.firstName,'')||'_'||
> COALESCE(cust.lastName,'') AS Name,
> l1.description AS Format,
> l2.description AS Artist,
> l3.description AS Title,
> NVL(SUM(invD.invoicedQty),0.00)::NUMERIC(16,2) AS Qty,
> (NVL(SUM(invD.parts /
> invD.invoicedQty),0.00)+NVL(SUM(invD.labour),0.00))::NUMERIC(16,2) AS
> Cost,
> (NVL(SUM(invD.parts /
> invD.invoicedQty),0.00)+NVL(SUM(invD.labour),0.00)+NVL(SUM(invD.markUp / invD.invoicedQty),0.00))::NUMERIC(16,2) AS Selling,
> (NVL(SUM(invD.partsVAT /
> invD.invoicedQty),0.00)+NVL(SUM(invD.labourVAT),0.00))::NUMERIC(16,2)
> AS VAT,
> (NVL(SUM(invD.markUpPercentage),0.00))::NUMERIC(16,2) AS
> Margin_Percentage,
> (NVL(SUM(invD.parts),0.00)+NVL(SUM(invD.labour),0.00))::NUMERIC(16,2)
> AS Total_Cost,
> (NVL(SUM(invD.parts),0.00)+NVL(SUM(invD.labour),0.00)+NVL(SUM(invD.markUp),0.00))::NUMERIC(16,2) AS Total_Selling,
> (NVL(SUM(invD.partsVAT),0.00)+NVL(SUM(invD.labourVAT),0.00))::NUMERIC(16,2) AS Total_VAT,
> (NVL(SUM(invD.parts),0.00)+NVL(SUM(invD.partsVAT),0.00)+NVL(SUM(invD.labour),0.00)+NVL(SUM(invD.labourVAT),0.00)+NVL(SUM(invD.markUp),0.00))::NUMERIC(16,2) AS Invoice_Total
> FROM salesInvHdr AS invH
> JOIN salesInvDet AS invD
> JOIN sourceDetUpgr AS srcU
> JOIN supplierProduct AS suppP
> JOIN product AS prod
> JOIN productLevelDet AS l1
> ON prod.l1ProductLevelDetRecNo = l1.recNo
> JOIN productLevelDet AS l2
> ON prod.l2ProductLevelDetRecNo = l2.recNo
> JOIN productLevelDet AS l3
> ON prod.l3ProductLevelDetRecNo = l3.recNo
> ON suppP.productRecNo = prod.recNo
> ON srcU.supplierProductRecNo = suppP.recNo
> ON invD.POOrPackageRecNo = srcU.recNo
> ON invH.recNo = invD.salesInvHdrRecNo
> AND invD.lineType = 'P'
>
> JOIN sourceDet AS srcD
> JOIN sourceHdr AS srcH
> JOIN customer AS cust
> ON srcH.customerRecNo = cust.recNo
> ON srcD.sourceHdrRecNo = srcH.recNo
> JOIN sourceDetExtRef AS srcE
> JOIN clientBranch AS clntB
> JOIN client AS clnt
> ON clntB.clientRecNo = clnt.recNo
> AND clnt.code LIKE UPPER('XXX')||'%'
> ON srcE.ownerForeignRecNo = clntB.recNo
> ON srcE.foreignRecNo = srcD.recNo
> AND srcE.tableName = 'sourcedet'
> AND srcE.ownerForeignTableName = 'clientbranch'
> ON invH.sourceDetRecNo = srcD.recNo
> AND srcD.actionStatus != 'V'
> WHERE invH.serviceCoBranchRecNo = 2
> AND (invH.invoiceDate BETWEEN '01/09/2008' AND '30/09/2008')
> GROUP BY 1,2,3,4,5,6,7,8
> ORDER BY 1 ;
>

Steve Tucknott
ReTSol Ltd

DDI: 01323 488548
Mobile: 0773 671 5772

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Obe, Regina 2008-10-03 11:02:32 Re: CASCADING update
Previous Message A B 2008-10-03 09:47:49 CASCADING update