Thread: Forcing order of Joins etc

Forcing order of Joins etc

From

Steve T

Date:

03 October 2008, 09:02:05

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 ;

Re: Forcing order of Joins etc

From

Steve T

Date:

03 October 2008, 09:59:12

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

Re: Forcing order of Joins etc

From

Steve T

Date:

03 October 2008, 11:30:21

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

Re: Forcing order of Joins etc

From

Tom Lane

Date:

03 October 2008, 12:38:15

Steve T <steve@retsol.co.uk> writes:
> 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.

So that was in fact unrelated to your problem.  Maybe it was just that
auto-analyze caught up with changes you'd made to the table contents?

            regards, tom lane

Re: Forcing order of Joins etc

From

Steve T

Date:

03 October 2008, 12:53:08

Tom,
I don't think the increase in due to an analyse.
The database is static - I have a live server with the database on and I take a daily copy to my laptop. So my laptop version is static during the day - and I have been running the development tests of the script against the laptop copy. So the increase in performance was against static data.
Stupidly, I didn't save a copy of the explain plan before I changed the 'join_...' setting, so I don't know why its running so much faster.

I did then try running the script on the live server, but it seriously affected performance, so I had to abort it. But I ran an 'explain' version on the live and laptop and I can only see one major difference - that is on how it reads the invoice header.

The explains are attached if they shows anything.

PS the live server is 8.0.8 and the laptop is 8.1.0

PPS Is there any chance that I'm seeing an illusion with the 'join_collate...' setting and that the real impact was caused by taking the server down and bringing it back up again? That could then explain the speed up and why it has remained faster.

On Fri, 2008-10-03 at 08:38 -0400, Tom Lane wrote:

Steve T <steve@retsol.co.uk> writes:
> 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.

So that was in fact unrelated to your problem.  Maybe it was just that
auto-analyze caught up with changes you'd made to the table contents?

			regards, tom lane

Steve Tucknott
ReTSol Ltd

DDI: 01323 488548

Attachment

Re: Forcing order of Joins etc

From

Steve T

Date:

03 October 2008, 14:12:17

Tom,
I thought I'd try it on a backup server as well.
What this highlighted is the effect of caching?

I did the following:
Took an explain
Ran the query (took just over 4m)
Set the join_collapse_limit to 1
Ran the query and timed it (just under 1m)
Reset the join_collapse_limit to 8
Ran the query (just over 1m)
Took an explain

So that looked like the collapse limit wasn't being reset. Then I realised that the cache may be having an impact, so I ran a large query (select * on a table with 1million rows) - then:

Ran the query (took just over 4m)
Ran the query (took 26secs!)

So what I saw as a potential non-reset of the join collapse limit appears to be the effect of effective caching?

On Fri, 2008-10-03 at 08:38 -0400, Tom Lane wrote:

Steve T <steve@retsol.co.uk> writes:
> 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.

So that was in fact unrelated to your problem.  Maybe it was just that
auto-analyze caught up with changes you'd made to the table contents?

			regards, tom lane

Steve Tucknott
ReTSol Ltd

DDI: 01323 488548