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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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