optimize query using explain analyze result

Question

following is a part of my union all

SELECT tbl_12.AcGrCode
        ,tbl_12.AcId
        ,tbl_12.AcName
        ,tbl_11.AcgrName
        ,tbl_16.VrDate
        ,tbl_16.PDC
        ,tbl_16.VrDate::timestamp AS vdate
        ,tbl_16.VrNo
        ,to_char(tbl_16.vrdate, 'MM')::INT AS dateMonth
        ,to_char(tbl_16.vrdate, 'yyyy')::INT AS dateYear
        ,tbl_16.Refno
        ,tbl_02.VrName
        ,tbl_17.Narr
        ,CASE 
            WHEN tbl_16.VrId = 6
                THEN 0::DECIMAL
            ELSE tbl_17.Dr::DECIMAL
            END AS Dr
        ,CASE 
            WHEN tbl_16.VrId = 6
                THEN 0::DECIMAL
            ELSE tbl_17.Cr::DECIMAL
            END AS Cr
        ,tbl_16.AcyrId
        ,tbl_16.VrId
        ,(
            SELECT coalesce(SUM(tbl_17.Dr), 0::MONEY)
            FROM tbl_16
            INNER JOIN tbl_17 ON tbl_16.JrMId = tbl_17.JrmId
            WHERE tbl_16.BranchID = 1
                AND (tbl_17.AcId = tbl_12.AcId)
                AND (tbl_16.VrId = 6)
                AND (tbl_16.AcYrId = 6)
            ) AS OpDr
        ,(
            SELECT coalesce(SUM(tbl_17.Cr), 0::MONEY)
            FROM tbl_16
            INNER JOIN tbl_17 ON tbl_16.JrMId = tbl_17.JrmId
            WHERE tbl_16.BranchID = 1
                AND (tbl_17.AcId = tbl_12.AcId)
                AND (tbl_16.VrId = 6)
                AND (tbl_16.AcYrId = 6)
            ) AS OpCr
        ,(
            SELECT coalesce(SUM(tbl_17.Dr), 0::MONEY)
            FROM tbl_16
            INNER JOIN tbl_17 ON tbl_16.JrMId = tbl_17.JrmId
            WHERE tbl_16.BranchID = 1
                AND (tbl_17.AcId = tbl_12.AcId)
                AND (tbl_16.VrId <> 6)
                AND (tbl_16.AcYrId = 6)
                AND (
                    tbl_16.vrdate > Cast('2014-03-31' AS TIMESTAMP)
                    AND tbl_16.vrdate < Cast('2014-04-01' AS TIMESTAMP)
                    )
            ) AS OpperDr
        ,(
            SELECT coalesce(SUM(tbl_17.Cr), 0::MONEY)
            FROM tbl_16
            INNER JOIN tbl_17 ON tbl_16.JrMId = tbl_17.JrmId
            WHERE tbl_16.BranchID = 1
                AND (tbl_17.AcId = tbl_12.AcId)
                AND (tbl_16.VrId <> 6)
                AND (tbl_16.AcYrId = 6)
                AND (
                    tbl_16.vrdate > Cast('2014-03-31' AS TIMESTAMP)
                    AND tbl_16.vrdate < Cast('2014-04-01' AS TIMESTAMP)
                    )
            ) AS OpperCr
        ,tbl_47.AreaName
        ,tbl_16.JrmId
    FROM tbl_16
    INNER JOIN tbl_02 ON tbl_16.VrId = tbl_02.VrId
    INNER JOIN tbl_17 ON tbl_16.JrMId = tbl_17.JrmId
    INNER JOIN tbl_12 ON tbl_17.AcId = tbl_12.AcId
    INNER JOIN tbl_11 ON tbl_12.AcGrCode = tbl_11.AcgrCode
    INNER JOIN tbl_01 ON tbl_16.AcyrId = tbl_01.AcYrId
    LEFT OUTER JOIN tbl_22 ON tbl_16.RepId = tbl_22.RepId
    LEFT OUTER JOIN tbl_47 ON tbl_12.AreaId = tbl_47.AreaId
    WHERE tbl_16.BranchID = 1
        AND vrdate BETWEEN Cast('2014-04-01' AS TIMESTAMP)
            AND Cast('2014-04-30' AS TIMESTAMP)
        AND (tbl_16.AcYrId = 6)
        AND tbl_16.VrId <> 6
        AND tbl_12.acid <> 1

This select is executing very slowly, I just take explain analyze of this select and I get the following stats.I don't know how to use this to improve the query performance

explain-analyze

Thorsten Kettner · Accepted Answer · 2015-04-17 10:45:49Z

1

One doesn't need an execution plan to see that you are selecting the same data again and again. Of course this is slow. Move your subqueries to one derived table expression and use this instead.

SELECT tbl_12.AcGrCode
        ,tbl_12.AcId
        ,tbl_12.AcName
        ,tbl_11.AcgrName
        ,tbl_16.VrDate
        ,tbl_16.PDC
        ,tbl_16.VrDate::timestamp AS vdate
        ,tbl_16.VrNo
        ,to_char(tbl_16.vrdate, 'MM')::INT AS dateMonth
        ,to_char(tbl_16.vrdate, 'yyyy')::INT AS dateYear
        ,tbl_16.Refno
        ,tbl_02.VrName
        ,tbl_17.Narr
        ,CASE WHEN tbl_16.VrId = 6 THEN 0::DECIMAL ELSE tbl_17.Dr::DECIMAL END AS Dr
        ,CASE WHEN tbl_16.VrId = 6 THEN 0::DECIMAL ELSE tbl_17.Cr::DECIMAL END AS Cr
        ,tbl_16.AcyrId
        ,tbl_16.VrId
        ,coalesce(agg.OpDr, 0::MONEY) AS OpDr
        ,coalesce(agg.OpCr, 0::MONEY) AS OpCr
        ,coalesce(agg.OpperDr, 0::MONEY) AS OpperDr
        ,coalesce(agg.OpperCr, 0::MONEY) AS OpperCr
        ,tbl_47.AreaName
        ,tbl_16.JrmId
    FROM tbl_16
    INNER JOIN tbl_02 ON tbl_16.VrId = tbl_02.VrId
    INNER JOIN tbl_17 ON tbl_16.JrMId = tbl_17.JrmId
    INNER JOIN tbl_12 ON tbl_17.AcId = tbl_12.AcId
    INNER JOIN tbl_11 ON tbl_12.AcGrCode = tbl_11.AcgrCode
    INNER JOIN tbl_01 ON tbl_16.AcyrId = tbl_01.AcYrId
    LEFT OUTER JOIN tbl_22 ON tbl_16.RepId = tbl_22.RepId
    LEFT OUTER JOIN tbl_47 ON tbl_12.AreaId = tbl_47.AreaId
    LEFT OUTER JOIN
    (
      SELECT 
        t17.AcId, 
        SUM(t17.Dr) AS OpDr,
        SUM(t17.Cr) AS OpCr,
        SUM(CASE WHEN t16.vrdate > Cast('2014-03-31' AS TIMESTAMP) AND t16.vrdate < Cast('2014-04-01' AS TIMESTAMP) THEN t17.Dr END) AS OpperDr,
        SUM(CASE WHEN t16.vrdate > Cast('2014-03-31' AS TIMESTAMP) AND t16.vrdate < Cast('2014-04-01' AS TIMESTAMP) THEN t17.Cr END) AS OpperCr
      FROM tbl_16 t16
      INNER JOIN tbl_17 t17 ON t16.JrMId = t17.JrmId
      WHERE t16.BranchID = 1 AND t16.VrId <> 6 AND t16.AcYrId = 6
      GROUP BY t17.AcId
    ) AS agg ON agg.AcId = tbl_12.AcId
    WHERE tbl_16.BranchID = 1
      AND vrdate BETWEEN Cast('2014-04-01' AS TIMESTAMP) AND Cast('2014-04-30' AS TIMESTAMP)
      AND tbl_16.AcYrId = 6
      AND tbl_16.VrId <> 6
      AND tbl_12.acid <> 1

edited Apr 17, 2015 at 10:45

answered Apr 17, 2015 at 9:46

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Thorsten Kettner Over a year ago

Thanks for accepting my answer. I've edited it though and moved COALESCE out of the derived table, because otherwise you'd get NULLs when there are no matching records at all.

carl Over a year ago

actually i need to return 0 instead of NULL that's why i used coalesce

Thorsten Kettner Over a year ago

And this is why I moved it outside the derived table and select now coalesce(agg.OpDr, 0::MONEY) AS OpDr etc.

Collectives™ on Stack Overflow

optimize query using explain analyze result

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related