Skip site navigation (1) Skip section navigation (2)

Forcing order of Joins etc

From: Steve T <steve(at)retsol(dot)co(dot)uk>
To: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Forcing order of Joins etc
Date: 2008-10-03 09:01:45
Message-ID: 1223024505.3598.18.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: 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 ;
     

Responses

pgsql-novice by date

Next:From: A BDate: 2008-10-03 09:47:49
Subject: CASCADING update
Previous:From: Dobes VandermeerDate: 2008-10-03 08:51:06
Subject: Optimizing sum() operations

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group