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
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