Re: Forcing order of Joins etc - Mailing list pgsql-novice

Apologies - RTFM.
I see the psql SHOW command shows the variables and SET then allows them to be changed.

So in my case, I can now see that the join_collapse_limit has indeed been  set back to 8 - but I'm still getting the improved query speed.

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

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

Steve Tucknott ReTSol Ltd DDI:         01323 488548