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 11:30:13
Message-ID: 1223033413.3598.44.camel@localhost.localdomain
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Sean Davis 2008-10-03 11:51:38 Re: Optimizing sum() operations
Previous Message Obe, Regina 2008-10-03 11:02:32 Re: CASCADING update