Very ineffective plan with merge join - Mailing list pgsql-hackers
| From | Oleg Bartunov |
|---|---|
| Subject | Very ineffective plan with merge join |
| Date | |
| Msg-id | Pine.LNX.4.64.1004151602450.7097@sn.sai.msu.ru Whole thread Raw |
| Responses |
Re: Very ineffective plan with merge join
Re: Very ineffective plan with merge join |
| List | pgsql-hackers |
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_RRRefIS 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_RRRefIS 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_RRRefIS 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._RecorderRRefAND _AccRgED7200_TED1._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED1._Period =
_AccRg7175_R._PeriodAND _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._RecorderRRefAND _AccRgED7200_TED2._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED2._Period =
_AccRg7175_R._PeriodAND _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._RecorderRRefAND _AccRgED7200_TED3._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED3._Period =
_AccRg7175_R._PeriodAND _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_RRRefIS 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_RRRefIS 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_RRRefIS 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._RecorderRRefAND _AccRgED7200_TED1._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED1._Period =
_AccRg7175_R._PeriodAND _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._RecorderRRefAND _AccRgED7200_TED2._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED2._Period =
_AccRg7175_R._PeriodAND _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._RecorderRRefAND _AccRgED7200_TED3._LineNo = _AccRg7175_R._LineNo AND _AccRgED7200_TED3._Period =
_AccRg7175_R._PeriodAND _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.91rows=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.105rows=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.43rows=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=3498930width=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.41rows=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=224535width=169) (actual time=60823.699..75507.579 rows=9189 loops=1)
HashCond: (_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=9189loops=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.20rows=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.43rows=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.96rows=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.251rows=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
LeftJoin (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.127rows=235636 loops=1)
Recheck Cond: ((_period >= '2009-10-01 00:00:00'::timestamp without time zone) AND (_period <= '2009-10-31
23:59:59'::timestampwithout 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.54rows=237384 width=0) (actual time=38.541..38.541 rows=235636 loops=1)
Index Cond: ((_period >= '2009-10-01
00:00:00'::timestampwithout time zone) AND (_period <= '2009-10-31 23:59:59'::timestamp without time zone))
-> Hash (cost=1.27..1.27 rows=27 width=20)
(actualtime=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.024rows=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.64rows=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.64rows=183 width=40) (actual time=0.017..0.214 rows=183 loops=1)
Filter: (_lineno = 2::numeric) -> Hash
(cost=13.64..13.64rows=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.241rows=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.477rows=133029 loops=1) Hash Cond:
(rd3722586b56744409b0f43cff4e9ed7a9_rd._idrref= public.tt2._reffieldrref) -> Merge Right Join
(cost=2039405.34..2084101.13rows=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.789rows=2688878 loops=1) Filter: (_correspond = 1::numeric)
-> Materialize (cost=1426831.70..1428791.65 rows=156796 width=185) (actual
time=70888.014..70986.427rows=133029 loops=1) -> Sort (cost=1426831.70..1427223.69
rows=156796width=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.412rows=133029 loops=1) Hash Cond:
(_accrg7175_r._accountctrref= _acc7_extdim7144_tedacc3._acc7_idrref) ->
MergeRight 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.59rows=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=2615289loops=1) Sort Key:
_accrged7200_ted2._lineno,_accrged7200_ted2._recordertref, _accrged7200_ted2._recorderrref, _accrged7200_ted2._period,
_accrged7200_ted2._kindrref Sort Method:
externalmerge 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=156796width=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=133029loops=1) Hash Cond:
(_accrg7175_r._accountctrref= rd3722586b56744409b0f43cff4e9ed7a9_rd._idrref)
-> Bitmap Heap Scan on _accrg7175 _accrg7175_r (cost=10305.89..52129.11
rows=237384width=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.54rows=237384 width=0) (actual time=32.274..32.274 rows=235636 loops=1)
Index Cond: ((_period >= '2009-10-01
00:00:00'::timestampwithout time zone) AND (_period <= '2009-10-31 23:59:59'::timestamp without time zone))
-> Hash (cost=1.27..1.27 rows=27 width=22)
(actualtime=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.019rows=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.64rows=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.64rows=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.174rows=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)
(actualtime=41.752..7882.665 rows=142218 loops=1) -> Hash Semi Join (cost=10357.17..4205325.63
rows=216219width=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=224535width=169) (actual time=41.647..553.835 rows=9189 loops=1) Hash Cond:
(_accrg7175_r._accountdtrref= _acc7_extdim7144_tedacc1._acc7_idrref) -> Nested Loop
LeftJoin (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
LeftJoin (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.317rows=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.19rows=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.731rows=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)
(actualtime=40.254..180.210 rows=235636 loops=1)
RecheckCond: ((_period >= '2009-10-01 00:00:00'::timestamp without time zone) AND (_period <= '2009-10-31
23:59:59'::timestampwithout time zone)) Filter:
(_activeAND (_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.54rows=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=27width=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_rnnon _accrged7200 _accrged7200_ted3 (cost=0.00..7.79 rows=1 width=96) (actual
time=0.014..0.016rows=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.278rows=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
Scanusing _accntr7200_byrecorder_rnn on _accrged7200 _accrged7200_ted2 (cost=0.00..7.79 rows=1 width=96) (actual
time=0.012..0.013rows=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=301loops=1) -> Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc1
(cost=0.00..13.64rows=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)
(actualtime=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=150989width=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=156796width=185) (actual time=35.697..4657.771 rows=133029 loops=1) Hash Cond:
(_accrg7175_r._accountctrref= _acc7_extdim7144_tedacc1._acc7_idrref) -> Nested Loop
LeftJoin (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
LeftJoin (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)
(actualtime=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.39rows=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.023rows=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)
(actualtime=34.705..190.450 rows=235636 loops=1)
RecheckCond: ((_period >= '2009-10-01 00:00:00'::timestamp without time zone) AND (_period <= '2009-10-31
23:59:59'::timestampwithout time zone)) Filter:
(_activeAND (_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.54rows=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=27width=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_rnnon _accrged7200 _accrged7200_ted3 (cost=0.00..7.79 rows=1 width=96) (actual
time=0.013..0.014rows=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.275rows=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
Scanusing _accntr7200_byrecorder_rnn on _accrged7200 _accrged7200_ted2 (cost=0.00..7.79 rows=1 width=96) (actual
time=0.012..0.013rows=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=301loops=1) -> Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc1
(cost=0.00..13.64rows=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)
(actualtime=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: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
pgsql-hackers by date: