Forcing order of Joins etc - Mailing list pgsql-novice

From Steve T
Subject Forcing order of Joins etc
Date
Msg-id 1223024505.3598.18.camel@localhost.localdomain
Whole thread Raw
Responses Re: Forcing order of Joins etc
List pgsql-novice
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 ;
    

pgsql-novice by date:

Previous
From: "Dobes Vandermeer"
Date:
Subject: Optimizing sum() operations
Next
From: "A B"
Date:
Subject: CASCADING update