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

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 (view raw or flat)
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

pgsql-novice by date

Next:From: Obe, ReginaDate: 2008-10-03 11:02:32
Subject: Re: CASCADING update
Previous:From: A BDate: 2008-10-03 09:47:49
Subject: CASCADING update

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