Hi there,
below is an example of interesting query and two plans - the bad plan, which
uses merge join and big sorting, took 216 sec, and good plan with merge join disabled took
8 sec. Sorry for odd names, they were generated by popular accounting
engine in Russia. 8.4.3 and HEAD show the same behaviour.
The query:
--set enable_mergejoin to off;
explain analyze
SELECT
_V8TblAli1_Q_000_T_001._AccountRRef AS f_3,
_V8TblAli1_Q_000_T_001._Value1_TYPE AS f_4,
_V8TblAli1_Q_000_T_001._Value1_RTRef AS f_5,
_V8TblAli1_Q_000_T_001._Value1_RRRef AS f_6,
_V8TblAli1_Q_000_T_001._Value2_TYPE AS f_7,
_V8TblAli1_Q_000_T_001._Value2_RTRef AS f_8,
_V8TblAli1_Q_000_T_001._Value2_RRRef AS f_9,
_V8TblAli1_Q_000_T_001._Value3_TYPE AS f_10,
_V8TblAli1_Q_000_T_001._Value3_RTRef AS f_11,
_V8TblAli1_Q_000_T_001._Value3_RRRef AS f_12,
0 AS f_13,
0 AS f_14,
0 AS f_15,
0 AS f_16,
SUM(_V8TblAli1_Q_000_T_001._Fld7178TurnoverCt) AS f_17,
SUM(_V8TblAli1_Q_000_T_001._Fld7180TurnoverCt) AS f_18,
SUM(_V8TblAli1_Q_000_T_001._Fld7178TurnoverCt) AS f_19,
SUM(_V8TblAli1_Q_000_T_001._Fld7180TurnoverCt) AS f_20,
SUM(_V8TblAli1_Q_000_T_001._Fld7178TurnoverCt) AS f_21,
SUM(_V8TblAli1_Q_000_T_001._Fld7180TurnoverCt) AS f_22
FROM
(
SELECT
_V8TblAli1_R._Period AS _Period,
_V8TblAli1_R._RecorderTRef AS _RecorderTRef,
_V8TblAli1_R._RecorderRRef AS _RecorderRRef,
_V8TblAli1_R._AccountRRef AS _AccountRRef,
_V8TblAli1_R._Value1_TYPE AS _Value1_TYPE,
_V8TblAli1_R._Value1_RTRef AS _Value1_RTRef,
_V8TblAli1_R._Value1_RRRef AS _Value1_RRRef,
_V8TblAli1_R._Value2_TYPE AS _Value2_TYPE,
_V8TblAli1_R._Value2_RTRef AS _Value2_RTRef,
_V8TblAli1_R._Value2_RRRef AS _Value2_RRRef,
_V8TblAli1_R._Value3_TYPE AS _Value3_TYPE,
_V8TblAli1_R._Value3_RTRef AS _Value3_RTRef,
_V8TblAli1_R._Value3_RRRef AS _Value3_RRRef,
CASE
WHEN SUM(_V8TblAli1_R._Fld7178TurnoverCt) IS NULL
THEN CAST(0 AS NUMERIC(22,2))
ELSE SUM(_V8TblAli1_R._Fld7178TurnoverCt)
END AS _Fld7178TurnoverCt,
CASE
WHEN SUM(_V8TblAli1_R._Fld7180TurnoverCt) IS NULL
THEN CAST(0 AS NUMERIC(22,3))
ELSE SUM(_V8TblAli1_R._Fld7180TurnoverCt)
END AS _Fld7180TurnoverCt
FROM
(
SELECT
_AccRg7175_R._Period AS _Period,
_AccRg7175_R._RecorderTRef AS _RecorderTRef,
_AccRg7175_R._RecorderRRef AS _RecorderRRef,
_AccRg7175_R._AccountDtRRef AS _AccountRRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED1 = TRUE
THEN CASE
WHEN _AccRgED7200_TED1._Value_TYPE = '\\001'::bytea OR
_AccRgED7200_TED1._Value_TYPE = '\\010'::bytea AND
_AccRgED7200_TED1._Value_RTRef IS NOT NULL AND _AccRgED7200_TED1._Value_RRRef
IS NOT NULL
THEN _AccRgED7200_TED1._Value_TYPE
ELSE NULL
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\001'::bytea OR
CAST(NULL AS BYTEA) = '\\010'::bytea AND CAST(NULL AS BYTEA) IS NOT NULL
THEN CAST(NULL AS BYTEA)
ELSE NULL
END
END AS _Value1_TYPE,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED1 = TRUE
THEN CASE
WHEN _AccRgED7200_TED1._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED1._Value_RTRef
WHEN _AccRgED7200_TED1._Value_TYPE IS NULL OR
_AccRgED7200_TED1._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
END AS _Value1_RTRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED1 = TRUE
THEN CASE
WHEN _AccRgED7200_TED1._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED1._Value_RRRef
WHEN _AccRgED7200_TED1._Value_TYPE IS NULL OR
_AccRgED7200_TED1._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
END AS _Value1_RRRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED2 = TRUE
THEN CASE
WHEN _AccRgED7200_TED2._Value_TYPE = '\\001'::bytea OR
_AccRgED7200_TED2._Value_TYPE = '\\010'::bytea AND
_AccRgED7200_TED2._Value_RTRef IS NOT NULL AND _AccRgED7200_TED2._Value_RRRef
IS NOT NULL
THEN _AccRgED7200_TED2._Value_TYPE
ELSE NULL
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\001'::bytea OR
CAST(NULL AS BYTEA) = '\\010'::bytea AND CAST(NULL AS BYTEA) IS NOT NULL
THEN CAST(NULL AS BYTEA)
ELSE NULL
END
END AS _Value2_TYPE,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED2 = TRUE
THEN CASE
WHEN _AccRgED7200_TED2._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED2._Value_RTRef
WHEN _AccRgED7200_TED2._Value_TYPE IS NULL OR
_AccRgED7200_TED2._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
END AS _Value2_RTRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED2 = TRUE
THEN CASE
WHEN _AccRgED7200_TED2._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED2._Value_RRRef
WHEN _AccRgED7200_TED2._Value_TYPE IS NULL OR
_AccRgED7200_TED2._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
END AS _Value2_RRRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED3 = TRUE
THEN CASE
WHEN _AccRgED7200_TED3._Value_TYPE = '\\001'::bytea OR
_AccRgED7200_TED3._Value_TYPE = '\\010'::bytea AND
_AccRgED7200_TED3._Value_RTRef IS NOT NULL AND _AccRgED7200_TED3._Value_RRRef
IS NOT NULL
THEN _AccRgED7200_TED3._Value_TYPE
ELSE NULL
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\001'::bytea OR
CAST(NULL AS BYTEA) = '\\010'::bytea AND CAST(NULL AS BYTEA) IS NOT NULL
THEN CAST(NULL AS BYTEA)
ELSE NULL
END
END AS _Value3_TYPE,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED3 = TRUE
THEN CASE
WHEN _AccRgED7200_TED3._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED3._Value_RTRef
WHEN _AccRgED7200_TED3._Value_TYPE IS NULL OR
_AccRgED7200_TED3._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
END AS _Value3_RTRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED3 = TRUE
THEN CASE
WHEN _AccRgED7200_TED3._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED3._Value_RRRef
WHEN _AccRgED7200_TED3._Value_TYPE IS NULL OR
_AccRgED7200_TED3._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
END AS _Value3_RRRef,
CAST(0 AS NUMERIC(15,2)) AS _Fld7178TurnoverCt,
CAST(0 AS NUMERIC(15,3)) AS _Fld7180TurnoverCt
FROM
_AccRg7175 _AccRg7175_R
INNER JOIN tt3 RD3722586b56744409b0f43cff4e9ed7a9_RD
ON RD3722586b56744409b0f43cff4e9ed7a9_RD._IDRRef = _AccRg7175_R._AccountDtRRef
LEFT OUTER JOIN _Acc7_ExtDim7144 _Acc7_ExtDim7144_TEDAcc1
ON _Acc7_ExtDim7144_TEDAcc1._Acc7_IDRRef = _AccRg7175_R._AccountDtRRef AND
_Acc7_ExtDim7144_TEDAcc1._LineNo = 1
LEFT OUTER JOIN _AccRgED7200 _AccRgED7200_TED1
ON _AccRgED7200_TED1._RecorderTRef = _AccRg7175_R._RecorderTRef AND
_AccRgED7200_TED1._RecorderRRef = _AccRg7175_R._RecorderRRef AND
_AccRgED7200_TED1._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED1._Period
= _AccRg7175_R._Period AND _AccRgED7200_TED1._Correspond = 0 AND
_AccRgED7200_TED1._KindRRef = _Acc7_ExtDim7144_TEDAcc1._DimKindRRef
LEFT OUTER JOIN _Acc7_ExtDim7144 _Acc7_ExtDim7144_TEDAcc2
ON _Acc7_ExtDim7144_TEDAcc2._Acc7_IDRRef = _AccRg7175_R._AccountDtRRef AND
_Acc7_ExtDim7144_TEDAcc2._LineNo = 2
LEFT OUTER JOIN _AccRgED7200 _AccRgED7200_TED2
ON _AccRgED7200_TED2._RecorderTRef = _AccRg7175_R._RecorderTRef AND
_AccRgED7200_TED2._RecorderRRef = _AccRg7175_R._RecorderRRef AND
_AccRgED7200_TED2._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED2._Period
= _AccRg7175_R._Period AND _AccRgED7200_TED2._Correspond = 0 AND
_AccRgED7200_TED2._KindRRef = _Acc7_ExtDim7144_TEDAcc2._DimKindRRef
LEFT OUTER JOIN _Acc7_ExtDim7144 _Acc7_ExtDim7144_TEDAcc3
ON _Acc7_ExtDim7144_TEDAcc3._Acc7_IDRRef = _AccRg7175_R._AccountDtRRef AND
_Acc7_ExtDim7144_TEDAcc3._LineNo = 3
LEFT OUTER JOIN _AccRgED7200 _AccRgED7200_TED3
ON _AccRgED7200_TED3._RecorderTRef = _AccRg7175_R._RecorderTRef AND
_AccRgED7200_TED3._RecorderRRef = _AccRg7175_R._RecorderRRef AND
_AccRgED7200_TED3._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED3._Period
= _AccRg7175_R._Period AND _AccRgED7200_TED3._Correspond = 0 AND
_AccRgED7200_TED3._KindRRef = _Acc7_ExtDim7144_TEDAcc3._DimKindRRef
WHERE
_AccRg7175_R._Active = TRUE AND _AccRg7175_R._AccountDtRRef IN (SELECT
tt2._REFFIELDRRef AS f_1
FROM
tt2) AND _AccRg7175_R._Fld7176RRef =
'\\270\\366\\277J\\304\\243\\004\\354N\\356\\342\\225\\021\\026\\253{'::bytea AND
_AccRg7175_R._Period >= '2009-10-01 00:00:00'::timestamp AND _AccRg7175_R._Period
<= '2009-10-31 23:59:59'::timestamp
UNION ALL
(SELECT
_AccRg7175_R._Period AS _Period,
_AccRg7175_R._RecorderTRef AS _RecorderTRef,
_AccRg7175_R._RecorderRRef AS _RecorderRRef,
_AccRg7175_R._AccountCtRRef AS _AccountRRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED1 = TRUE
THEN CASE
WHEN _AccRgED7200_TED1._Value_TYPE = '\\001'::bytea OR
_AccRgED7200_TED1._Value_TYPE = '\\010'::bytea AND
_AccRgED7200_TED1._Value_RTRef IS NOT NULL AND _AccRgED7200_TED1._Value_RRRef
IS NOT NULL
THEN _AccRgED7200_TED1._Value_TYPE
ELSE NULL
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\001'::bytea OR
CAST(NULL AS BYTEA) = '\\010'::bytea AND CAST(NULL AS BYTEA) IS NOT NULL
THEN CAST(NULL AS BYTEA)
ELSE NULL
END
END AS _Value1_TYPE,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED1 = TRUE
THEN CASE
WHEN _AccRgED7200_TED1._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED1._Value_RTRef
WHEN _AccRgED7200_TED1._Value_TYPE IS NULL OR
_AccRgED7200_TED1._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
END AS _Value1_RTRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED1 = TRUE
THEN CASE
WHEN _AccRgED7200_TED1._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED1._Value_RRRef
WHEN _AccRgED7200_TED1._Value_TYPE IS NULL OR
_AccRgED7200_TED1._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
END AS _Value1_RRRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED2 = TRUE
THEN CASE
WHEN _AccRgED7200_TED2._Value_TYPE = '\\001'::bytea OR
_AccRgED7200_TED2._Value_TYPE = '\\010'::bytea AND
_AccRgED7200_TED2._Value_RTRef IS NOT NULL AND _AccRgED7200_TED2._Value_RRRef
IS NOT NULL
THEN _AccRgED7200_TED2._Value_TYPE
ELSE NULL
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\001'::bytea OR
CAST(NULL AS BYTEA) = '\\010'::bytea AND CAST(NULL AS BYTEA) IS NOT NULL
THEN CAST(NULL AS BYTEA)
ELSE NULL
END
END AS _Value2_TYPE,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED2 = TRUE
THEN CASE
WHEN _AccRgED7200_TED2._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED2._Value_RTRef
WHEN _AccRgED7200_TED2._Value_TYPE IS NULL OR
_AccRgED7200_TED2._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
END AS _Value2_RTRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED2 = TRUE
THEN CASE
WHEN _AccRgED7200_TED2._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED2._Value_RRRef
WHEN _AccRgED7200_TED2._Value_TYPE IS NULL OR
_AccRgED7200_TED2._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
END AS _Value2_RRRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED3 = TRUE
THEN CASE
WHEN _AccRgED7200_TED3._Value_TYPE = '\\001'::bytea OR
_AccRgED7200_TED3._Value_TYPE = '\\010'::bytea AND
_AccRgED7200_TED3._Value_RTRef IS NOT NULL AND _AccRgED7200_TED3._Value_RRRef
IS NOT NULL
THEN _AccRgED7200_TED3._Value_TYPE
ELSE NULL
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\001'::bytea OR
CAST(NULL AS BYTEA) = '\\010'::bytea AND CAST(NULL AS BYTEA) IS NOT NULL
THEN CAST(NULL AS BYTEA)
ELSE NULL
END
END AS _Value3_TYPE,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED3 = TRUE
THEN CASE
WHEN _AccRgED7200_TED3._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED3._Value_RTRef
WHEN _AccRgED7200_TED3._Value_TYPE IS NULL OR
_AccRgED7200_TED3._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE '\\000\\000\\000\\000'::bytea
END
END AS _Value3_RTRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._ED3 = TRUE
THEN CASE
WHEN _AccRgED7200_TED3._Value_TYPE = '\\010'::bytea
THEN _AccRgED7200_TED3._Value_RRRef
WHEN _AccRgED7200_TED3._Value_TYPE IS NULL OR
_AccRgED7200_TED3._Value_TYPE NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
ELSE CASE
WHEN CAST(NULL AS BYTEA) = '\\010'::bytea
THEN CAST(NULL AS BYTEA)
WHEN CAST(NULL AS BYTEA) IS NULL OR
CAST(NULL AS BYTEA) NOT IN ('\\001'::bytea,'\\010'::bytea)
THEN NULL
ELSE
'\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'::bytea
END
END AS _Value3_RRRef,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._RF1 = TRUE
THEN _AccRg7175_R._Fld7178
ELSE CAST(0 AS NUMERIC(15,2))
END AS _Fld7178TurnoverCt,
CASE
WHEN RD3722586b56744409b0f43cff4e9ed7a9_RD._RF2 = TRUE
THEN _AccRg7175_R._Fld7180Ct
ELSE CAST(0 AS NUMERIC(15,3))
END AS _Fld7180TurnoverCt
FROM
_AccRg7175 _AccRg7175_R
INNER JOIN tt3 RD3722586b56744409b0f43cff4e9ed7a9_RD
ON RD3722586b56744409b0f43cff4e9ed7a9_RD._IDRRef = _AccRg7175_R._AccountCtRRef
LEFT OUTER JOIN _Acc7_ExtDim7144 _Acc7_ExtDim7144_TEDAcc1
ON _Acc7_ExtDim7144_TEDAcc1._Acc7_IDRRef = _AccRg7175_R._AccountCtRRef AND
_Acc7_ExtDim7144_TEDAcc1._LineNo = 1
LEFT OUTER JOIN _AccRgED7200 _AccRgED7200_TED1
ON _AccRgED7200_TED1._RecorderTRef = _AccRg7175_R._RecorderTRef AND
_AccRgED7200_TED1._RecorderRRef = _AccRg7175_R._RecorderRRef AND
_AccRgED7200_TED1._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED1._Period
= _AccRg7175_R._Period AND _AccRgED7200_TED1._Correspond = 1 AND
_AccRgED7200_TED1._KindRRef = _Acc7_ExtDim7144_TEDAcc1._DimKindRRef
LEFT OUTER JOIN _Acc7_ExtDim7144 _Acc7_ExtDim7144_TEDAcc2
ON _Acc7_ExtDim7144_TEDAcc2._Acc7_IDRRef = _AccRg7175_R._AccountCtRRef AND
_Acc7_ExtDim7144_TEDAcc2._LineNo = 2
LEFT OUTER JOIN _AccRgED7200 _AccRgED7200_TED2
ON _AccRgED7200_TED2._RecorderTRef = _AccRg7175_R._RecorderTRef AND
_AccRgED7200_TED2._RecorderRRef = _AccRg7175_R._RecorderRRef AND
_AccRgED7200_TED2._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED2._Period
= _AccRg7175_R._Period AND _AccRgED7200_TED2._Correspond = 1 AND
_AccRgED7200_TED2._KindRRef = _Acc7_ExtDim7144_TEDAcc2._DimKindRRef
LEFT OUTER JOIN _Acc7_ExtDim7144 _Acc7_ExtDim7144_TEDAcc3
ON _Acc7_ExtDim7144_TEDAcc3._Acc7_IDRRef = _AccRg7175_R._AccountCtRRef AND
_Acc7_ExtDim7144_TEDAcc3._LineNo = 3
LEFT OUTER JOIN _AccRgED7200 _AccRgED7200_TED3
ON _AccRgED7200_TED3._RecorderTRef = _AccRg7175_R._RecorderTRef AND
_AccRgED7200_TED3._RecorderRRef = _AccRg7175_R._RecorderRRef AND
_AccRgED7200_TED3._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED3._Period
= _AccRg7175_R._Period AND _AccRgED7200_TED3._Correspond = 1 AND
_AccRgED7200_TED3._KindRRef = _Acc7_ExtDim7144_TEDAcc3._DimKindRRef
WHERE
_AccRg7175_R._Active = TRUE AND _AccRg7175_R._AccountCtRRef IN (SELECT
tt2._REFFIELDRRef AS f_2
FROM
tt2) AND _AccRg7175_R._Fld7176RRef =
'\\270\\366\\277J\\304\\243\\004\\354N\\356\\342\\225\\021\\026\\253{'::bytea AND
_AccRg7175_R._Period >= '2009-10-01 00:00:00'::timestamp AND _AccRg7175_R._Period
<= '2009-10-31 23:59:59'::timestamp)
) _V8TblAli1_R
GROUP BY
_V8TblAli1_R._Period,
_V8TblAli1_R._RecorderTRef,
_V8TblAli1_R._RecorderRRef,
_V8TblAli1_R._AccountRRef,
_V8TblAli1_R._Value1_TYPE,
_V8TblAli1_R._Value1_RTRef,
_V8TblAli1_R._Value1_RRRef,
_V8TblAli1_R._Value2_TYPE,
_V8TblAli1_R._Value2_RTRef,
_V8TblAli1_R._Value2_RRRef,
_V8TblAli1_R._Value3_TYPE,
_V8TblAli1_R._Value3_RTRef,
_V8TblAli1_R._Value3_RRRef
HAVING
CASE
WHEN SUM(_V8TblAli1_R._Fld7178TurnoverCt) IS NULL
THEN CAST(0 AS NUMERIC(22,2))
ELSE SUM(_V8TblAli1_R._Fld7178TurnoverCt)
END <> 0 OR
CASE
WHEN SUM(_V8TblAli1_R._Fld7180TurnoverCt) IS NULL
THEN CAST(0 AS NUMERIC(22,3))
ELSE SUM(_V8TblAli1_R._Fld7180TurnoverCt)
END <> 0
) _V8TblAli1_Q_000_T_001
GROUP BY
_V8TblAli1_Q_000_T_001._AccountRRef,
_V8TblAli1_Q_000_T_001._Value1_TYPE,
_V8TblAli1_Q_000_T_001._Value1_RTRef,
_V8TblAli1_Q_000_T_001._Value1_RRRef,
_V8TblAli1_Q_000_T_001._Value2_TYPE,
_V8TblAli1_Q_000_T_001._Value2_RTRef,
_V8TblAli1_Q_000_T_001._Value2_RRRef,
_V8TblAli1_Q_000_T_001._Value3_TYPE,
_V8TblAli1_Q_000_T_001._Value3_RTRef,
_V8TblAli1_Q_000_T_001._Value3_RRRef
;
Bad plan (with merge join):
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=4654118.62..4654210.44 rows=3673 width=384) (actual
time=216257.221..216259.033 rows=2820 loops=1)
-> HashAggregate (cost=4650997.33..4652282.57 rows=36721 width=424)
(actual time=216222.361..216236.727 rows=9736 loops=1)
Filter: ((CASE WHEN (sum((0.00::numeric(15,2))) IS NULL) THEN 0.00::numeric(22,2)
ELSE sum((0.00::numeric(15,2))) END <> 0::numeric) OR (CASE WHEN
(sum((0.000::numeric(15,3))) IS NULL) THEN 0.000::numeric(22,3) ELSE
sum((0.000::numeric(15,3))) END <> 0::numeric))
-> Append (cost=2464212.81..4631718.91 rows=367208 width=424)
(actual time=104895.538..215848.161 rows=142218 loops=1)
-> Hash Semi Join (cost=2464212.81..2535057.73 rows=216219
width=158) (actual time=104895.536..119720.076 rows=9189 loops=1)
Hash Cond: (rd3722586b56744409b0f43cff4e9ed7a9_rd._idrref
= public.tt2._reffieldrref)
-> Merge Right Join (cost=2464210.69..2522330.91
rows=224535 width=175) (actual time=104895.456..119673.105 rows=9189 loops=1)
Merge Cond: ((_accrged7200_ted1._lineno =
_accrg7175_r._lineno) AND (_accrged7200_ted1._recordertref =
_accrg7175_r._recordertref) AND (_accrged7200_ted1._recorderrref =
_accrg7175_r._recorderrref) AND (_accrged7200_ted1._period =
_accrg7175_r._period) AND (_accrged7200_ted1._kindrref =
_acc7_extdim7144_tedacc1._dimkindrref))
-> Sort (cost=742487.10..751234.43 rows=3498930
width=96) (actual time=29370.349..42475.968 rows=3309482 loops=1)
Sort Key: _accrged7200_ted1._lineno,
_accrged7200_ted1._recordertref, _accrged7200_ted1._recorderrref,
_accrged7200_ted1._period, _accrged7200_ted1._kindrref
Sort Method: external merge Disk: 313648kB
-> Seq Scan on _accrged7200 _accrged7200_ted1
(cost=0.00..182790.96 rows=3498930 width=96) (actual time=0.042..3168.957
rows=3526745 loops=1)
Filter: (_correspond = 0::numeric)
-> Materialize (cost=1721719.07..1724525.76
rows=224535 width=169) (actual time=75524.000..75530.378 rows=9189 loops=1)
-> Sort (cost=1721719.07..1722280.41
rows=224535 width=169) (actual time=75523.995..75526.041 rows=9189 loops=1)
Sort Key: _accrg7175_r._lineno,
_accrg7175_r._recordertref, _accrg7175_r._recorderrref, _accrg7175_r._period,
_acc7_extdim7144_tedacc1._dimkindrref
Sort Method: quicksort Memory: 2825kB
-> Hash Left Join
(cost=1624587.41..1682574.75 rows=224535 width=169) (actual
time=60823.699..75507.579 rows=9189 loops=1)
Hash Cond:
(_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc1._acc7_idrref)
-> Merge Right Join
(cost=1624570.01..1679357.48 rows=152722 width=149) (actual
time=60823.337..75496.893 rows=9189 loops=1)
Merge Cond:
((_accrged7200_ted2._lineno = _accrg7175_r._lineno) AND
(_accrged7200_ted2._recordertref = _accrg7175_r._recordertref) AND
(_accrged7200_ted2._recorderrref = _accrg7175_r._recorderrref) AND
(_accrged7200_ted2._period = _accrg7175_r._period) AND
(_accrged7200_ted2._kindrref = _acc7_extdim7144_tedacc2._dimkindrref))
-> Sort
(cost=742487.10..751234.43 rows=3498930 width=96) (actual
time=30453.653..43480.714 rows=3309483 loops=1)
Sort Key:
_accrged7200_ted2._lineno, _accrged7200_ted2._recordertref,
_accrged7200_ted2._recorderrref, _accrged7200_ted2._period,
_accrged7200_ted2._kindrref
Sort Method: external
merge Disk: 313648kB
-> Seq Scan on
_accrged7200 _accrged7200_ted2 (cost=0.00..182790.96 rows=3498930 width=96)
(actual time=0.043..3193.851 rows=3526745 loops=1)
Filter:
(_correspond = 0::numeric)
-> Sort
(cost=882078.39..882460.20 rows=152722 width=136) (actual
time=30368.030..30369.492 rows=9189 loops=1)
Sort Key:
_accrg7175_r._lineno, _accrg7175_r._recordertref, _accrg7175_r._recorderrref,
_accrg7175_r._period, _acc7_extdim7144_tedacc2._dimkindrref
Sort Method:
quicksort Memory: 1677kB
-> Hash Left Join
(cost=811821.52..868928.61 rows=152722 width=136) (actual
time=30346.292..30359.777 rows=9189 loops=1)
Hash Cond:
(_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc2._acc7_idrref)
-> Merge Right
Join (cost=811805.59..866593.06 rows=152722 width=116) (actual
time=30345.980..30352.981 rows=9189 loops=1)
Merge
Cond: ((_accrged7200_ted3._lineno = _accrg7175_r._lineno) AND
(_accrged7200_ted3._recordertref = _accrg7175_r._recordertref) AND
(_accrged7200_ted3._recorderrref = _accrg7175_r._recorderrref) AND
(_accrged7200_ted3._period = _accrg7175_r._period) AND
(_accrged7200_ted3._kindrref = _acc7_extdim7144_tedacc3._dimkindrref))
-> Sort
(cost=742487.10..751234.43 rows=3498930 width=96) (actual
time=30106.208..30106.208 rows=1 loops=1)
Sort
Key: _accrged7200_ted3._lineno, _accrged7200_ted3._recordertref,
_accrged7200_ted3._recorderrref, _accrged7200_ted3._period,
_accrged7200_ted3._kindrref
Sort
Method: external merge Disk: 313648kB
->
Seq Scan on _accrged7200 _accrged7200_ted3 (cost=0.00..182790.96 rows=3498930
width=96) (actual time=0.055..3222.022 rows=3526745 loops=1)
Filter: (_correspond = 0::numeric)
-> Sort
(cost=69313.98..69695.78 rows=152722 width=103) (actual time=239.762..241.251
rows=9189 loops=1)
Sort
Key: _accrg7175_r._lineno, _accrg7175_r._recordertref,
_accrg7175_r._recorderrref, _accrg7175_r._period,
_acc7_extdim7144_tedacc3._dimkindrref
Sort
Method: quicksort Memory: 1677kB
->
Hash Left Join (cost=10321.72..56164.19 rows=152722 width=103) (actual
time=40.905..214.010 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref =
_acc7_extdim7144_tedacc3._acc7_idrref)
-> Hash Join (cost=10307.50..54844.86 rows=152722 width=83) (actual
time=40.658..210.357 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref =
rd3722586b56744409b0f43cff4e9ed7a9_rd._idrref)
-> Bitmap Heap Scan on _accrg7175 _accrg7175_r (cost=10305.89..52129.11
rows=237384 width=63) (actual time=40.286..169.127 rows=235636 loops=1)
Recheck Cond: ((_period >= '2009-10-01 00:00:00'::timestamp without time
zone) AND (_period <= '2009-10-31 23:59:59'::timestamp without time zone))
Filter: (_active AND (_fld7176rref =
'\\270\\366\\277J\\304\\243\\004\\354N\\356\\342\\225\\021\\026\\253{'::bytea))
-> Bitmap Index Scan on _accntr7175_byperiod_trn
(cost=0.00..10246.54 rows=237384 width=0) (actual time=38.541..38.541 rows=235636
loops=1)
Index Cond: ((_period >= '2009-10-01 00:00:00'::timestamp without
time zone) AND (_period <= '2009-10-31 23:59:59'::timestamp without time zone))
-> Hash (cost=1.27..1.27 rows=27 width=20) (actual time=0.038..0.038
rows=27 loops=1)
-> Seq Scan on tt3 rd3722586b56744409b0f43cff4e9ed7a9_rd
(cost=0.00..1.27 rows=27 width=20) (actual time=0.017..0.024 rows=27 loops=1)
-> Hash (cost=13.64..13.64 rows=47 width=40) (actual time=0.214..0.214
rows=47 loops=1)
-> Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc3
(cost=0.00..13.64 rows=47 width=40) (actual time=0.021..0.187 rows=47 loops=1)
Filter: (_lineno = 3::numeric)
-> Hash
(cost=13.64..13.64 rows=183 width=40) (actual time=0.279..0.279 rows=183 loops=1)
-> Seq
Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc2 (cost=0.00..13.64 rows=183
width=40) (actual time=0.017..0.214 rows=183 loops=1)
Filter: (_lineno = 2::numeric)
-> Hash (cost=13.64..13.64
rows=301 width=40) (actual time=0.339..0.339 rows=301 loops=1)
-> Seq Scan on
_acc7_extdim7144 _acc7_extdim7144_tedacc1 (cost=0.00..13.64 rows=301 width=40)
(actual time=0.017..0.241 rows=301 loops=1)
Filter: (_lineno =
1::numeric)
-> Hash (cost=1.50..1.50 rows=50 width=17) (actual
time=0.039..0.039 rows=50 loops=1)
-> Seq Scan on tt2 (cost=0.00..1.50 rows=50
width=17) (actual time=0.014..0.019 rows=50 loops=1)
-> Hash Semi Join (cost=2039407.47..2092989.10 rows=150989
width=174) (actual time=95481.121..96101.477 rows=133029 loops=1)
Hash Cond: (rd3722586b56744409b0f43cff4e9ed7a9_rd._idrref
= public.tt2._reffieldrref)
-> Merge Right Join (cost=2039405.34..2084101.13
rows=156796 width=191) (actual time=95481.015..95694.282 rows=133029 loops=1)
Merge Cond: ((_accrged7200_ted3._lineno =
_accrg7175_r._lineno) AND (_accrged7200_ted3._recordertref =
_accrg7175_r._recordertref) AND (_accrged7200_ted3._recorderrref =
_accrg7175_r._recorderrref) AND (_accrged7200_ted3._period =
_accrg7175_r._period) AND (_accrged7200_ted3._kindrref =
_acc7_extdim7144_tedacc3._dimkindrref))
-> Sort (cost=612570.13..619364.40 rows=2717706
width=96) (actual time=24592.987..24592.987 rows=1 loops=1)
Sort Key: _accrged7200_ted3._lineno,
_accrged7200_ted3._recordertref, _accrged7200_ted3._recorderrref,
_accrged7200_ted3._period, _accrged7200_ted3._kindrref
Sort Method: external merge Disk: 239128kB
-> Seq Scan on _accrged7200 _accrged7200_ted3
(cost=0.00..182790.96 rows=2717706 width=96) (actual time=0.041..3061.789
rows=2688878 loops=1)
Filter: (_correspond = 1::numeric)
-> Materialize (cost=1426831.70..1428791.65
rows=156796 width=185) (actual time=70888.014..70986.427 rows=133029 loops=1)
-> Sort (cost=1426831.70..1427223.69
rows=156796 width=185) (actual time=70888.000..70950.276 rows=133029 loops=1)
Sort Key: _accrg7175_r._lineno,
_accrg7175_r._recordertref, _accrg7175_r._recorderrref, _accrg7175_r._period,
_acc7_extdim7144_tedacc3._dimkindrref
Sort Method: external sort Disk:
20472kB
-> Hash Left Join
(cost=1354500.46..1398828.86 rows=156796 width=185) (actual
time=59815.616..70065.412 rows=133029 loops=1)
Hash Cond:
(_accrg7175_r._accountctrref = _acc7_extdim7144_tedacc3._acc7_idrref)
-> Merge Right Join
(cost=1354486.24..1397614.07 rows=156796 width=165) (actual
time=59815.410..70002.244 rows=133029 loops=1)
Merge Cond:
((_accrged7200_ted1._lineno = _accrg7175_r._lineno) AND
(_accrged7200_ted1._recordertref = _accrg7175_r._recordertref) AND
(_accrged7200_ted1._recorderrref = _accrg7175_r._recorderrref) AND
(_accrged7200_ted1._period = _accrg7175_r._period) AND
(_accrged7200_ted1._kindrref = _acc7_extdim7144_tedacc1._dimkindrref))
-> Sort
(cost=612570.13..619364.40 rows=2717706 width=96) (actual
time=24329.193..32784.613 rows=2615288 loops=1)
Sort Key:
_accrged7200_ted1._lineno, _accrged7200_ted1._recordertref,
_accrged7200_ted1._recorderrref, _accrged7200_ted1._period,
_accrged7200_ted1._kindrref
Sort Method: external
merge Disk: 239128kB
-> Seq Scan on
_accrged7200 _accrged7200_ted1 (cost=0.00..182790.96 rows=2717706 width=96)
(actual time=0.055..2979.799 rows=2688878 loops=1)
Filter:
(_correspond = 1::numeric)
-> Sort
(cost=741912.60..742304.59 rows=156796 width=152) (actual
time=35485.713..35553.329 rows=133029 loops=1)
Sort Key:
_accrg7175_r._lineno, _accrg7175_r._recordertref, _accrg7175_r._recorderrref,
_accrg7175_r._period, _acc7_extdim7144_tedacc1._dimkindrref
Sort Method: external
sort Disk: 19040kB
-> Hash Left Join
(cost=682861.76..728382.25 rows=156796 width=152) (actual
time=24654.198..34674.682 rows=133029 loops=1)
Hash Cond:
(_accrg7175_r._accountctrref = _acc7_extdim7144_tedacc1._acc7_idrref)
-> Merge Right
Join (cost=682844.36..725972.19 rows=156796 width=132) (actual
time=24653.911..34586.342 rows=133029 loops=1)
Merge
Cond: ((_accrged7200_ted2._lineno = _accrg7175_r._lineno) AND
(_accrged7200_ted2._recordertref = _accrg7175_r._recordertref) AND
(_accrged7200_ted2._recorderrref = _accrg7175_r._recorderrref) AND
(_accrged7200_ted2._period = _accrg7175_r._period) AND
(_accrged7200_ted2._kindrref = _acc7_extdim7144_tedacc2._dimkindrref))
-> Sort
(cost=612570.13..619364.40 rows=2717706 width=96) (actual
time=23915.426..32219.262 rows=2615289 loops=1)
Sort
Key: _accrged7200_ted2._lineno, _accrged7200_ted2._recordertref,
_accrged7200_ted2._recorderrref, _accrged7200_ted2._period,
_accrged7200_ted2._kindrref
Sort
Method: external merge Disk: 239128kB
->
Seq Scan on _accrged7200 _accrged7200_ted2 (cost=0.00..182790.96 rows=2717706
width=96) (actual time=0.046..2938.496 rows=2688878 loops=1)
Filter: (_correspond = 1::numeric)
-> Sort
(cost=70270.72..70662.71 rows=156796 width=119) (actual time=738.094..758.161
rows=133029 loops=1)
Sort
Key: _accrg7175_r._lineno, _accrg7175_r._recordertref,
_accrg7175_r._recorderrref, _accrg7175_r._period,
_acc7_extdim7144_tedacc2._dimkindrref
Sort
Method: quicksort Memory: 24852kB
->
Hash Left Join (cost=10323.42..56740.38 rows=156796 width=119) (actual
time=34.758..319.411 rows=133029 loops=1)
Hash Cond: (_accrg7175_r._accountctrref =
_acc7_extdim7144_tedacc2._acc7_idrref)
-> Hash Join (cost=10307.50..54885.60 rows=156796 width=99) (actual
time=34.428..249.381 rows=133029 loops=1)
Hash Cond: (_accrg7175_r._accountctrref =
rd3722586b56744409b0f43cff4e9ed7a9_rd._idrref)
-> Bitmap Heap Scan on _accrg7175 _accrg7175_r (cost=10305.89..52129.11
rows=237384 width=77) (actual time=34.372..148.718 rows=235636 loops=1)
Recheck Cond: ((_period >= '2009-10-01 00:00:00'::timestamp without time
zone) AND (_period <= '2009-10-31 23:59:59'::timestamp without time zone))
Filter: (_active AND (_fld7176rref =
'\\270\\366\\277J\\304\\243\\004\\354N\\356\\342\\225\\021\\026\\253{'::bytea))
-> Bitmap Index Scan on _accntr7175_byperiod_trn
(cost=0.00..10246.54 rows=237384 width=0) (actual time=32.274..32.274 rows=235636
loops=1)
Index Cond: ((_period >= '2009-10-01 00:00:00'::timestamp without
time zone) AND (_period <= '2009-10-31 23:59:59'::timestamp without time zone))
-> Hash (cost=1.27..1.27 rows=27 width=22) (actual time=0.034..0.034
rows=27 loops=1)
-> Seq Scan on tt3 rd3722586b56744409b0f43cff4e9ed7a9_rd
(cost=0.00..1.27 rows=27 width=22) (actual time=0.012..0.019 rows=27 loops=1)
-> Hash (cost=13.64..13.64 rows=183 width=40) (actual time=0.297..0.297
rows=183 loops=1)
-> Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc2
(cost=0.00..13.64 rows=183 width=40) (actual time=0.014..0.213 rows=183 loops=1)
Filter: (_lineno = 2::numeric)
-> Hash
(cost=13.64..13.64 rows=301 width=40) (actual time=0.266..0.266 rows=301 loops=1)
-> Seq
Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc1 (cost=0.00..13.64 rows=301
width=40) (actual time=0.016..0.187 rows=301 loops=1)
Filter: (_lineno = 1::numeric)
-> Hash (cost=13.64..13.64
rows=47 width=40) (actual time=0.185..0.185 rows=47 loops=1)
-> Seq Scan on
_acc7_extdim7144 _acc7_extdim7144_tedacc3 (cost=0.00..13.64 rows=47 width=40)
(actual time=0.020..0.174 rows=47 loops=1)
Filter: (_lineno =
3::numeric)
-> Hash (cost=1.50..1.50 rows=50 width=17) (actual
time=0.041..0.041 rows=50 loops=1)
-> Seq Scan on tt2 (cost=0.00..1.50 rows=50
width=17) (actual time=0.010..0.018 rows=50 loops=1)
Total runtime: 216806.458 ms
(123 rows)
Time: 216860.579 ms
Good plan (merge join disabled):
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=7977566.70..7977658.52 rows=3673 width=384) (actual
time=8350.543..8351.983 rows=2820 loops=1)
-> HashAggregate (cost=7974445.41..7975730.65 rows=36721 width=424)
(actual time=8318.429..8331.366 rows=9736 loops=1)
Filter: ((CASE WHEN (sum((0.00::numeric(15,2))) IS NULL) THEN 0.00::numeric(22,2)
ELSE sum((0.00::numeric(15,2))) END <> 0::numeric) OR (CASE WHEN
(sum((0.000::numeric(15,3))) IS NULL) THEN 0.000::numeric(22,3) ELSE
sum((0.000::numeric(15,3))) END <> 0::numeric))
-> Append (cost=10357.17..7955166.99 rows=367208 width=424) (actual
time=41.752..7882.665 rows=142218 loops=1)
-> Hash Semi Join (cost=10357.17..4205325.63 rows=216219
width=158) (actual time=41.750..737.562 rows=9189 loops=1)
Hash Cond: (rd3722586b56744409b0f43cff4e9ed7a9_rd._idrref
= public.tt2._reffieldrref)
-> Nested Loop Left Join (cost=10355.05..4192598.81
rows=224535 width=175) (actual time=41.676..697.153 rows=9189 loops=1)
Join Filter: ((_accrged7200_ted1._period =
_accrg7175_r._period) AND (_accrged7200_ted1._kindrref =
_acc7_extdim7144_tedacc1._dimkindrref))
-> Hash Left Join (cost=10355.05..2449303.33
rows=224535 width=169) (actual time=41.647..553.835 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref =
_acc7_extdim7144_tedacc1._acc7_idrref)
-> Nested Loop Left Join
(cost=10337.65..2446086.07 rows=152722 width=149) (actual time=41.261..545.390
rows=9189 loops=1)
Join Filter: ((_accrged7200_ted2._period
= _accrg7175_r._period) AND (_accrged7200_ted2._kindrref =
_acc7_extdim7144_tedacc2._dimkindrref))
-> Hash Left Join
(cost=10337.65..1252292.90 rows=152722 width=136) (actual time=41.238..405.817
rows=9189 loops=1)
Hash Cond:
(_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc2._acc7_idrref)
-> Nested Loop Left Join
(cost=10321.72..1249957.36 rows=152722 width=116) (actual time=40.943..397.317
rows=9189 loops=1)
Join Filter:
((_accrged7200_ted3._period = _accrg7175_r._period) AND
(_accrged7200_ted3._kindrref = _acc7_extdim7144_tedacc3._dimkindrref))
-> Hash Left Join
(cost=10321.72..56164.19 rows=152722 width=103) (actual time=40.854..231.789
rows=9189 loops=1)
Hash Cond:
(_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc3._acc7_idrref)
-> Hash Join
(cost=10307.50..54844.86 rows=152722 width=83) (actual time=40.609..226.731
rows=9189 loops=1)
Hash Cond:
(_accrg7175_r._accountdtrref = rd3722586b56744409b0f43cff4e9ed7a9_rd._idrref)
-> Bitmap Heap
Scan on _accrg7175 _accrg7175_r (cost=10305.89..52129.11 rows=237384 width=63)
(actual time=40.254..180.210 rows=235636 loops=1)
Recheck Cond:
((_period >= '2009-10-01 00:00:00'::timestamp without time zone) AND (_period
<= '2009-10-31 23:59:59'::timestamp without time zone))
Filter:
(_active AND (_fld7176rref =
'\\270\\366\\277J\\304\\243\\004\\354N\\356\\342\\225\\021\\026\\253{'::bytea))
-> Bitmap
Index Scan on _accntr7175_byperiod_trn (cost=0.00..10246.54 rows=237384 width=0)
(actual time=37.747..37.747 rows=235636 loops=1)
Index
Cond: ((_period >= '2009-10-01 00:00:00'::timestamp without time zone) AND
(_period <= '2009-10-31 23:59:59'::timestamp without time zone))
-> Hash
(cost=1.27..1.27 rows=27 width=20) (actual time=0.021..0.021 rows=27 loops=1)
-> Seq
Scan on tt3 rd3722586b56744409b0f43cff4e9ed7a9_rd (cost=0.00..1.27 rows=27
width=20) (actual time=0.005..0.011 rows=27 loops=1)
-> Hash
(cost=13.64..13.64 rows=47 width=40) (actual time=0.208..0.208 rows=47 loops=1)
-> Seq Scan on
_acc7_extdim7144 _acc7_extdim7144_tedacc3 (cost=0.00..13.64 rows=47 width=40)
(actual time=0.020..0.183 rows=47 loops=1)
Filter:
(_lineno = 3::numeric)
-> Index Scan using
_accntr7200_byrecorder_rnn on _accrged7200 _accrged7200_ted3 (cost=0.00..7.79
rows=1 width=96) (actual time=0.014..0.016 rows=2 loops=9189)
Index Cond:
((_accrged7200_ted3._recordertref = _accrg7175_r._recordertref) AND
(_accrged7200_ted3._recorderrref = _accrg7175_r._recorderrref) AND
(_accrged7200_ted3._lineno = _accrg7175_r._lineno) AND
(_accrged7200_ted3._correspond = 0::numeric))
-> Hash (cost=13.64..13.64
rows=183 width=40) (actual time=0.278..0.278 rows=183 loops=1)
-> Seq Scan on
_acc7_extdim7144 _acc7_extdim7144_tedacc2 (cost=0.00..13.64 rows=183 width=40)
(actual time=0.008..0.192 rows=183 loops=1)
Filter: (_lineno =
2::numeric)
-> Index Scan using
_accntr7200_byrecorder_rnn on _accrged7200 _accrged7200_ted2 (cost=0.00..7.79
rows=1 width=96) (actual time=0.012..0.013 rows=2 loops=9189)
Index Cond:
((_accrged7200_ted2._recordertref = _accrg7175_r._recordertref) AND
(_accrged7200_ted2._recorderrref = _accrg7175_r._recorderrref) AND
(_accrged7200_ted2._lineno = _accrg7175_r._lineno) AND
(_accrged7200_ted2._correspond = 0::numeric))
-> Hash (cost=13.64..13.64 rows=301
width=40) (actual time=0.370..0.370 rows=301 loops=1)
-> Seq Scan on _acc7_extdim7144
_acc7_extdim7144_tedacc1 (cost=0.00..13.64 rows=301 width=40) (actual
time=0.007..0.211 rows=301 loops=1)
Filter: (_lineno = 1::numeric)
-> Index Scan using _accntr7200_byrecorder_rnn on
_accrged7200 _accrged7200_ted1 (cost=0.00..7.74 rows=1 width=96) (actual
time=0.012..0.013 rows=2 loops=9189)
Index Cond: ((_accrged7200_ted1._recordertref
= _accrg7175_r._recordertref) AND (_accrged7200_ted1._recorderrref =
_accrg7175_r._recorderrref) AND (_accrged7200_ted1._lineno =
_accrg7175_r._lineno) AND (_accrged7200_ted1._correspond = 0::numeric))
-> Hash (cost=1.50..1.50 rows=50 width=17) (actual
time=0.040..0.040 rows=50 loops=1)
-> Seq Scan on tt2 (cost=0.00..1.50 rows=50
width=17) (actual time=0.017..0.024 rows=50 loops=1)
-> Hash Semi Join (cost=10357.17..3746169.29 rows=150989
width=174) (actual time=35.810..7111.685 rows=133029 loops=1)
Hash Cond: (rd3722586b56744409b0f43cff4e9ed7a9_rd._idrref
= public.tt2._reffieldrref)
-> Nested Loop Left Join (cost=10355.05..3737281.32
rows=156796 width=191) (actual time=35.718..6617.853 rows=133029 loops=1)
Join Filter: ((_accrged7200_ted1._period =
_accrg7175_r._period) AND (_accrged7200_ted1._kindrref =
_acc7_extdim7144_tedacc1._dimkindrref))
-> Hash Left Join (cost=10355.05..2511642.62
rows=156796 width=185) (actual time=35.697..4657.771 rows=133029 loops=1)
Hash Cond: (_accrg7175_r._accountctrref =
_acc7_extdim7144_tedacc1._acc7_idrref)
-> Nested Loop Left Join
(cost=10337.65..2509232.56 rows=156796 width=165) (actual time=35.329..4540.385
rows=133029 loops=1)
Join Filter: ((_accrged7200_ted2._period
= _accrg7175_r._period) AND (_accrged7200_ted2._kindrref =
_acc7_extdim7144_tedacc2._dimkindrref))
-> Hash Left Join
(cost=10337.65..1283593.86 rows=156796 width=152) (actual time=35.305..2585.275
rows=133029 loops=1)
Hash Cond:
(_accrg7175_r._accountctrref = _acc7_extdim7144_tedacc2._acc7_idrref)
-> Nested Loop Left Join
(cost=10321.72..1281739.08 rows=156796 width=132) (actual time=35.014..2470.783
rows=133029 loops=1)
Join Filter:
((_accrged7200_ted3._period = _accrg7175_r._period) AND
(_accrged7200_ted3._kindrref = _acc7_extdim7144_tedacc3._dimkindrref))
-> Hash Left Join
(cost=10321.72..56100.39 rows=156796 width=119) (actual time=34.960..399.573
rows=133029 loops=1)
Hash Cond:
(_accrg7175_r._accountctrref = _acc7_extdim7144_tedacc3._acc7_idrref)
-> Hash Join
(cost=10307.50..54885.60 rows=156796 width=99) (actual time=34.749..330.023
rows=133029 loops=1)
Hash Cond:
(_accrg7175_r._accountctrref = rd3722586b56744409b0f43cff4e9ed7a9_rd._idrref)
-> Bitmap Heap
Scan on _accrg7175 _accrg7175_r (cost=10305.89..52129.11 rows=237384 width=77)
(actual time=34.705..190.450 rows=235636 loops=1)
Recheck Cond:
((_period >= '2009-10-01 00:00:00'::timestamp without time zone) AND (_period
<= '2009-10-31 23:59:59'::timestamp without time zone))
Filter:
(_active AND (_fld7176rref =
'\\270\\366\\277J\\304\\243\\004\\354N\\356\\342\\225\\021\\026\\253{'::bytea))
-> Bitmap
Index Scan on _accntr7175_byperiod_trn (cost=0.00..10246.54 rows=237384 width=0)
(actual time=33.015..33.015 rows=235636 loops=1)
Index
Cond: ((_period >= '2009-10-01 00:00:00'::timestamp without time zone) AND
(_period <= '2009-10-31 23:59:59'::timestamp without time zone))
-> Hash
(cost=1.27..1.27 rows=27 width=22) (actual time=0.027..0.027 rows=27 loops=1)
-> Seq
Scan on tt3 rd3722586b56744409b0f43cff4e9ed7a9_rd (cost=0.00..1.27 rows=27
width=22) (actual time=0.004..0.012 rows=27 loops=1)
-> Hash
(cost=13.64..13.64 rows=47 width=40) (actual time=0.186..0.186 rows=47 loops=1)
-> Seq Scan on
_acc7_extdim7144 _acc7_extdim7144_tedacc3 (cost=0.00..13.64 rows=47 width=40)
(actual time=0.016..0.158 rows=47 loops=1)
Filter:
(_lineno = 3::numeric)
-> Index Scan using
_accntr7200_byrecorder_rnn on _accrged7200 _accrged7200_ted3 (cost=0.00..7.79
rows=1 width=96) (actual time=0.013..0.014 rows=2 loops=133029)
Index Cond:
((_accrged7200_ted3._recordertref = _accrg7175_r._recordertref) AND
(_accrged7200_ted3._recorderrref = _accrg7175_r._recorderrref) AND
(_accrged7200_ted3._lineno = _accrg7175_r._lineno) AND
(_accrged7200_ted3._correspond = 1::numeric))
-> Hash (cost=13.64..13.64
rows=183 width=40) (actual time=0.275..0.275 rows=183 loops=1)
-> Seq Scan on
_acc7_extdim7144 _acc7_extdim7144_tedacc2 (cost=0.00..13.64 rows=183 width=40)
(actual time=0.008..0.200 rows=183 loops=1)
Filter: (_lineno =
2::numeric)
-> Index Scan using
_accntr7200_byrecorder_rnn on _accrged7200 _accrged7200_ted2 (cost=0.00..7.79
rows=1 width=96) (actual time=0.012..0.013 rows=2 loops=133029)
Index Cond:
((_accrged7200_ted2._recordertref = _accrg7175_r._recordertref) AND
(_accrged7200_ted2._recorderrref = _accrg7175_r._recorderrref) AND
(_accrged7200_ted2._lineno = _accrg7175_r._lineno) AND
(_accrged7200_ted2._correspond = 1::numeric))
-> Hash (cost=13.64..13.64 rows=301
width=40) (actual time=0.354..0.354 rows=301 loops=1)
-> Seq Scan on _acc7_extdim7144
_acc7_extdim7144_tedacc1 (cost=0.00..13.64 rows=301 width=40) (actual
time=0.009..0.244 rows=301 loops=1)
Filter: (_lineno = 1::numeric)
-> Index Scan using _accntr7200_byrecorder_rnn on
_accrged7200 _accrged7200_ted1 (cost=0.00..7.79 rows=1 width=96) (actual
time=0.011..0.013 rows=2 loops=133029)
Index Cond: ((_accrged7200_ted1._recordertref
= _accrg7175_r._recordertref) AND (_accrged7200_ted1._recorderrref =
_accrg7175_r._recorderrref) AND (_accrged7200_ted1._lineno =
_accrg7175_r._lineno) AND (_accrged7200_ted1._correspond = 1::numeric))
-> Hash (cost=1.50..1.50 rows=50 width=17) (actual
time=0.029..0.029 rows=50 loops=1)
-> Seq Scan on tt2 (cost=0.00..1.50 rows=50
width=17) (actual time=0.005..0.013 rows=50 loops=1)
Total runtime: 8354.318 ms
(85 rows)
Time: 8391.169 ms
Test data can be downloaded (38 Mb) from
http://www.sai.msu.su/~megera/postgres/files/merge_join_pb.dump.gz
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
