Re: Very ineffective plan with merge join

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: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:

> Sorry for odd names, they were generated by popular accounting
> engine in Russia.

How much of that can you trim out and still see the problem?

-Kevin

On Thu, 15 Apr 2010, Kevin Grittner wrote:

> Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:
>
>> Sorry for odd names, they were generated by popular accounting
>> engine in Russia.
>
> How much of that can you trim out and still see the problem?

It's difficult, since I don't know semantics of data. I reduced query, though.

query:

explain analyze

SELECT
_AccRg7175_R._Period AS _Period,
_AccRg7175_R._RecorderTRef AS _RecorderTRef,
_AccRg7175_R._RecorderRRef AS _RecorderRRef,
_AccRg7175_R._AccountDtRRef AS _AccountRRef
FROM
_AccRg7175 _AccRg7175_R
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 _Acc7_ExtDim7144 _Acc7_ExtDim7144_TEDAcc2
ON _Acc7_ExtDim7144_TEDAcc2._Acc7_IDRRef = _AccRg7175_R._AccountDtRRef
AND _Acc7_ExtDim7144_TEDAcc2._LineNo = 2
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._Period >= '2009-10-01 00:00:00'::timestamp
AND _AccRg7175_R._Period <= '2009-10-31 23:59:59'::timestamp
;

default plan:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=762035.09..822357.84 rows=224988 width=56) (actual time=25007.488..25022.338 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc1._acc7_idrref)
-> Hash Left Join (cost=762017.69..819134.13 rows=153030 width=56) (actual time=25007.173..25017.249 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc2._acc7_idrref)
-> Merge Right Join (cost=762001.76..816793.89 rows=153030 width=56) (actual time=25006.895..25012.218 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=694652.60..703399.93 rows=3498930 width=63) (actual time=24794.738..24794.738 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: 230896kB
-> Seq Scan on _accrged7200 _accrged7200_ted3 (cost=0.00..182790.96 rows=3498930 width=63) (actual time=0.153..3018.474 rows=3526745 loops=1)
Filter: (_correspond = 0::numeric)
-> Sort (cost=67344.64..67727.22 rows=153030 width=83) (actual time=212.145..213.289 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=10322.30..54166.12 rows=153030 width=83) (actual time=39.489..184.046 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc3._acc7_idrref)
-> Hash Join (cost=10308.08..52844.15 rows=153030 width=63) (actual time=39.256..180.388 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref = tt2._reffieldrref)
-> Bitmap Heap Scan on _accrg7175 _accrg7175_r (cost=10305.89..51535.65 rows=237384 width=63) (actual time=38.913..130.715 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
-> Bitmap Index Scan on _accntr7175_byperiod_trn (cost=0.00..10246.54 rows=237384 width=0) (actual time=37.281..37.281 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.88..1.88 rows=25 width=17) (actual time=0.077..0.077 rows=25 loops=1)
-> HashAggregate (cost=1.62..1.88 rows=25 width=17) (actual time=0.062..0.066 rows=25 loops=1)
-> Seq Scan on tt2 (cost=0.00..1.50 rows=50 width=17) (actual time=0.020..0.023 rows=50 loops=1)
-> Hash (cost=13.64..13.64 rows=47 width=40) (actual time=0.200..0.200 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.182 rows=47 loops=1)
Filter: (_lineno = 3::numeric)
-> Hash (cost=13.64..13.64 rows=183 width=20) (actual time=0.255..0.255 rows=183 loops=1)
-> Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc2 (cost=0.00..13.64 rows=183 width=20) (actual time=0.017..0.194 rows=183 loops=1)
Filter: (_lineno = 2::numeric)
-> Hash (cost=13.64..13.64 rows=301 width=20) (actual time=0.308..0.308 rows=301 loops=1)
-> Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc1 (cost=0.00..13.64 rows=301 width=20) (actual time=0.004..0.195 rows=301 loops=1)
Filter: (_lineno = 1::numeric)
Total runtime: 25114.486 ms
(36 rows)

Time: 25122.948 ms

no_merge plan:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=10355.63..934190.29 rows=224988 width=56) (actual time=33.522..273.552 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc1._acc7_idrref)
-> Hash Left Join (cost=10338.23..930966.57 rows=153030 width=56) (actual time=33.274..268.824 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc2._acc7_idrref)
-> Nested Loop Left Join (cost=10322.30..928626.34 rows=153030 width=56) (actual time=33.027..263.427 rows=9189 loops=1)
Join Filter: ((_accrged7200_ted3._period = _accrg7175_r._period) AND (_accrged7200_ted3._kindrref = _acc7_extdim7144_tedacc3._dimkindrref))
-> Hash Left Join (cost=10322.30..54166.12 rows=153030 width=83) (actual time=32.965..146.957 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc3._acc7_idrref)
-> Hash Join (cost=10308.08..52844.15 rows=153030 width=63) (actual time=32.790..143.699 rows=9189 loops=1)
Hash Cond: (_accrg7175_r._accountdtrref = tt2._reffieldrref)
-> Bitmap Heap Scan on _accrg7175 _accrg7175_r (cost=10305.89..51535.65 rows=237384 width=63) (actual time=32.541..105.359 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
-> Bitmap Index Scan on _accntr7175_byperiod_trn (cost=0.00..10246.54 rows=237384 width=0) (actual time=31.301..31.301 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.88..1.88 rows=25 width=17) (actual time=0.049..0.049 rows=25 loops=1)
-> HashAggregate (cost=1.62..1.88 rows=25 width=17) (actual time=0.035..0.039 rows=25 loops=1)
-> Seq Scan on tt2 (cost=0.00..1.50 rows=50 width=17) (actual time=0.009..0.012 rows=50 loops=1)
-> Hash (cost=13.64..13.64 rows=47 width=40) (actual time=0.154..0.154 rows=47 loops=1)
-> Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc3 (cost=0.00..13.64 rows=47 width=40) (actual time=0.015..0.137 rows=47 loops=1)
Filter: (_lineno = 3::numeric)
-> Index Scan using _accntr7200_byrecorder_rnn on _accrged7200 _accrged7200_ted3 (cost=0.00..5.69 rows=1 width=63) (actual time=0.010..0.011 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=20) (actual time=0.236..0.236 rows=183 loops=1)
-> Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc2 (cost=0.00..13.64 rows=183 width=20) (actual time=0.006..0.162 rows=183 loops=1)
Filter: (_lineno = 2::numeric)
-> Hash (cost=13.64..13.64 rows=301 width=20) (actual time=0.236..0.236 rows=301 loops=1)
-> Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc1 (cost=0.00..13.64 rows=301 width=20) (actual time=0.006..0.148 rows=301 loops=1)
Filter: (_lineno = 1::numeric)
Total runtime: 274.858 ms
(30 rows)

Time: 281.339 ms

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Hello

there is significant problem in statistics I think,

Regards
Pavel Stehule

2010/4/15 Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>:
> On Thu, 15 Apr 2010, Kevin Grittner wrote:
>
>> Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:
>>
>>> Sorry for odd names, they were generated by popular accounting
>>> engine in Russia.
>>
>> How much of that can you trim out and still see the problem?
>
> It's difficult, since I don't know semantics of data. I reduced query,
> though.
>
> query:
>
> explain analyze
>
> SELECT
> _AccRg7175_R._Period AS _Period,
> _AccRg7175_R._RecorderTRef AS _RecorderTRef,
> _AccRg7175_R._RecorderRRef AS _RecorderRRef,
> _AccRg7175_R._AccountDtRRef AS _AccountRRef
> FROM
> _AccRg7175 _AccRg7175_R
> 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 _Acc7_ExtDim7144 _Acc7_ExtDim7144_TEDAcc2
> ON _Acc7_ExtDim7144_TEDAcc2._Acc7_IDRRef = _AccRg7175_R._AccountDtRRef AND
> _Acc7_ExtDim7144_TEDAcc2._LineNo = 2
> 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._Period >= '2009-10-01
> 00:00:00'::timestamp AND _AccRg7175_R._Period <= '2009-10-31
> 23:59:59'::timestamp
> ;
>
> default plan:
>
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Hash Left Join  (cost=762035.09..822357.84 rows=224988 width=56) (actual
> time=25007.488..25022.338 rows=9189 loops=1)
>   Hash Cond: (_accrg7175_r._accountdtrref =
> _acc7_extdim7144_tedacc1._acc7_idrref)
>   ->  Hash Left Join  (cost=762017.69..819134.13 rows=153030 width=56)
> (actual time=25007.173..25017.249 rows=9189 loops=1)
>         Hash Cond: (_accrg7175_r._accountdtrref =
> _acc7_extdim7144_tedacc2._acc7_idrref)
>         ->  Merge Right Join  (cost=762001.76..816793.89 rows=153030
> width=56) (actual time=25006.895..25012.218 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=694652.60..703399.93 rows=3498930 width=63)
> (actual time=24794.738..24794.738 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: 230896kB
>                     ->  Seq Scan on _accrged7200 _accrged7200_ted3
>  (cost=0.00..182790.96 rows=3498930 width=63) (actual time=0.153..3018.474
> rows=3526745 loops=1)
>                           Filter: (_correspond = 0::numeric)
>               ->  Sort  (cost=67344.64..67727.22 rows=153030 width=83)
> (actual time=212.145..213.289 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=10322.30..54166.12 rows=153030
> width=83) (actual time=39.489..184.046 rows=9189 loops=1)
>                           Hash Cond: (_accrg7175_r._accountdtrref =
> _acc7_extdim7144_tedacc3._acc7_idrref)
>                           ->  Hash Join  (cost=10308.08..52844.15
> rows=153030 width=63) (actual time=39.256..180.388 rows=9189 loops=1)
>                                 Hash Cond: (_accrg7175_r._accountdtrref =
> tt2._reffieldrref)
>                                 ->  Bitmap Heap Scan on _accrg7175
> _accrg7175_r  (cost=10305.89..51535.65 rows=237384 width=63) (actual
> time=38.913..130.715 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
>                                       ->  Bitmap Index Scan on
> _accntr7175_byperiod_trn  (cost=0.00..10246.54 rows=237384 width=0) (actual
> time=37.281..37.281 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.88..1.88 rows=25 width=17)
> (actual time=0.077..0.077 rows=25 loops=1)
>                                       ->  HashAggregate  (cost=1.62..1.88
> rows=25 width=17) (actual time=0.062..0.066 rows=25 loops=1)
>                                             ->  Seq Scan on tt2
>  (cost=0.00..1.50 rows=50 width=17) (actual time=0.020..0.023 rows=50
> loops=1)
>                           ->  Hash  (cost=13.64..13.64 rows=47 width=40)
> (actual time=0.200..0.200 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.182 rows=47 loops=1)
>                                       Filter: (_lineno = 3::numeric)
>         ->  Hash  (cost=13.64..13.64 rows=183 width=20) (actual
> time=0.255..0.255 rows=183 loops=1)
>               ->  Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc2
>  (cost=0.00..13.64 rows=183 width=20) (actual time=0.017..0.194 rows=183
> loops=1)
>                     Filter: (_lineno = 2::numeric)
>   ->  Hash  (cost=13.64..13.64 rows=301 width=20) (actual time=0.308..0.308
> rows=301 loops=1)
>         ->  Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc1
>  (cost=0.00..13.64 rows=301 width=20) (actual time=0.004..0.195 rows=301
> loops=1)
>               Filter: (_lineno = 1::numeric)
>  Total runtime: 25114.486 ms
> (36 rows)
>
> Time: 25122.948 ms
>
> no_merge plan:
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Hash Left Join  (cost=10355.63..934190.29 rows=224988 width=56) (actual
> time=33.522..273.552 rows=9189 loops=1)
>   Hash Cond: (_accrg7175_r._accountdtrref =
> _acc7_extdim7144_tedacc1._acc7_idrref)
>   ->  Hash Left Join  (cost=10338.23..930966.57 rows=153030 width=56)
> (actual time=33.274..268.824 rows=9189 loops=1)
>         Hash Cond: (_accrg7175_r._accountdtrref =
> _acc7_extdim7144_tedacc2._acc7_idrref)
>         ->  Nested Loop Left Join  (cost=10322.30..928626.34 rows=153030
> width=56) (actual time=33.027..263.427 rows=9189 loops=1)
>               Join Filter: ((_accrged7200_ted3._period =
> _accrg7175_r._period) AND (_accrged7200_ted3._kindrref =
> _acc7_extdim7144_tedacc3._dimkindrref))
>               ->  Hash Left Join  (cost=10322.30..54166.12 rows=153030
> width=83) (actual time=32.965..146.957 rows=9189 loops=1)
>                     Hash Cond: (_accrg7175_r._accountdtrref =
> _acc7_extdim7144_tedacc3._acc7_idrref)
>                     ->  Hash Join  (cost=10308.08..52844.15 rows=153030
> width=63) (actual time=32.790..143.699 rows=9189 loops=1)
>                           Hash Cond: (_accrg7175_r._accountdtrref =
> tt2._reffieldrref)
>                           ->  Bitmap Heap Scan on _accrg7175 _accrg7175_r
>  (cost=10305.89..51535.65 rows=237384 width=63) (actual time=32.541..105.359
> 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
>                                 ->  Bitmap Index Scan on
> _accntr7175_byperiod_trn  (cost=0.00..10246.54 rows=237384 width=0) (actual
> time=31.301..31.301 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.88..1.88 rows=25 width=17)
> (actual time=0.049..0.049 rows=25 loops=1)
>                                 ->  HashAggregate  (cost=1.62..1.88 rows=25
> width=17) (actual time=0.035..0.039 rows=25 loops=1)
>                                       ->  Seq Scan on tt2  (cost=0.00..1.50
> rows=50 width=17) (actual time=0.009..0.012 rows=50 loops=1)
>                     ->  Hash  (cost=13.64..13.64 rows=47 width=40) (actual
> time=0.154..0.154 rows=47 loops=1)
>                           ->  Seq Scan on _acc7_extdim7144
> _acc7_extdim7144_tedacc3  (cost=0.00..13.64 rows=47 width=40) (actual
> time=0.015..0.137 rows=47 loops=1)
>                                 Filter: (_lineno = 3::numeric)
>               ->  Index Scan using _accntr7200_byrecorder_rnn on
> _accrged7200 _accrged7200_ted3  (cost=0.00..5.69 rows=1 width=63) (actual
> time=0.010..0.011 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=20) (actual
> time=0.236..0.236 rows=183 loops=1)
>               ->  Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc2
>  (cost=0.00..13.64 rows=183 width=20) (actual time=0.006..0.162 rows=183
> loops=1)
>                     Filter: (_lineno = 2::numeric)
>   ->  Hash  (cost=13.64..13.64 rows=301 width=20) (actual time=0.236..0.236
> rows=301 loops=1)
>         ->  Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc1
>  (cost=0.00..13.64 rows=301 width=20) (actual time=0.006..0.148 rows=301
> loops=1)
>               Filter: (_lineno = 1::numeric)
>  Total runtime: 274.858 ms
> (30 rows)
>
> Time: 281.339 ms
>
>
>
>        Regards,
>                Oleg
> _____________________________________________________________
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg(at)sai(dot)msu(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

On Thu, 15 Apr 2010, Pavel Stehule wrote:

> Hello
>
> there is significant problem in statistics I think,

Ah, you're right !

>
> Regards
> Pavel Stehule
>
> 2010/4/15 Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>:
>> On Thu, 15 Apr 2010, Kevin Grittner wrote:
>>
>>> Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> wrote:
>>>
>>>> Sorry for odd names, they were generated by popular accounting
>>>> engine in Russia.
>>>
>>> How much of that can you trim out and still see the problem?
>>
>> It's difficult, since I don't know semantics of data. I reduced query,
>> though.
>>
>> query:
>>
>> explain analyze
>>
>> SELECT
>> _AccRg7175_R._Period AS _Period,
>> _AccRg7175_R._RecorderTRef AS _RecorderTRef,
>> _AccRg7175_R._RecorderRRef AS _RecorderRRef,
>> _AccRg7175_R._AccountDtRRef AS _AccountRRef
>> FROM
>> _AccRg7175 _AccRg7175_R
>> 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 _Acc7_ExtDim7144 _Acc7_ExtDim7144_TEDAcc2
>> ON _Acc7_ExtDim7144_TEDAcc2._Acc7_IDRRef = _AccRg7175_R._AccountDtRRef AND
>> _Acc7_ExtDim7144_TEDAcc2._LineNo = 2
>> 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._Period >= '2009-10-01
>> 00:00:00'::timestamp AND _AccRg7175_R._Period <= '2009-10-31
>> 23:59:59'::timestamp
>> ;
>>
>> default plan:
>>
>> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>  Hash Left Join  (cost=762035.09..822357.84 rows=224988 width=56) (actual
>> time=25007.488..25022.338 rows=9189 loops=1)
>>   Hash Cond: (_accrg7175_r._accountdtrref =
>> _acc7_extdim7144_tedacc1._acc7_idrref)
>>   ->  Hash Left Join  (cost=762017.69..819134.13 rows=153030 width=56)
>> (actual time=25007.173..25017.249 rows=9189 loops=1)
>>         Hash Cond: (_accrg7175_r._accountdtrref =
>> _acc7_extdim7144_tedacc2._acc7_idrref)
>>         ->  Merge Right Join  (cost=762001.76..816793.89 rows=153030
>> width=56) (actual time=25006.895..25012.218 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=694652.60..703399.93 rows=3498930 width=63)
>> (actual time=24794.738..24794.738 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: 230896kB
>>                     ->  Seq Scan on _accrged7200 _accrged7200_ted3
>>  (cost=0.00..182790.96 rows=3498930 width=63) (actual time=0.153..3018.474
>> rows=3526745 loops=1)
>>                           Filter: (_correspond = 0::numeric)
>>               ->  Sort  (cost=67344.64..67727.22 rows=153030 width=83)
>> (actual time=212.145..213.289 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=10322.30..54166.12 rows=153030
>> width=83) (actual time=39.489..184.046 rows=9189 loops=1)
>>                           Hash Cond: (_accrg7175_r._accountdtrref =
>> _acc7_extdim7144_tedacc3._acc7_idrref)
>>                           ->  Hash Join  (cost=10308.08..52844.15
>> rows=153030 width=63) (actual time=39.256..180.388 rows=9189 loops=1)
>>                                 Hash Cond: (_accrg7175_r._accountdtrref =
>> tt2._reffieldrref)
>>                                 ->  Bitmap Heap Scan on _accrg7175
>> _accrg7175_r  (cost=10305.89..51535.65 rows=237384 width=63) (actual
>> time=38.913..130.715 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
>>                                       ->  Bitmap Index Scan on
>> _accntr7175_byperiod_trn  (cost=0.00..10246.54 rows=237384 width=0) (actual
>> time=37.281..37.281 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.88..1.88 rows=25 width=17)
>> (actual time=0.077..0.077 rows=25 loops=1)
>>                                       ->  HashAggregate  (cost=1.62..1.88
>> rows=25 width=17) (actual time=0.062..0.066 rows=25 loops=1)
>>                                             ->  Seq Scan on tt2
>>  (cost=0.00..1.50 rows=50 width=17) (actual time=0.020..0.023 rows=50
>> loops=1)
>>                           ->  Hash  (cost=13.64..13.64 rows=47 width=40)
>> (actual time=0.200..0.200 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.182 rows=47 loops=1)
>>                                       Filter: (_lineno = 3::numeric)
>>         ->  Hash  (cost=13.64..13.64 rows=183 width=20) (actual
>> time=0.255..0.255 rows=183 loops=1)
>>               ->  Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc2
>>  (cost=0.00..13.64 rows=183 width=20) (actual time=0.017..0.194 rows=183
>> loops=1)
>>                     Filter: (_lineno = 2::numeric)
>>   ->  Hash  (cost=13.64..13.64 rows=301 width=20) (actual time=0.308..0.308
>> rows=301 loops=1)
>>         ->  Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc1
>>  (cost=0.00..13.64 rows=301 width=20) (actual time=0.004..0.195 rows=301
>> loops=1)
>>               Filter: (_lineno = 1::numeric)
>>  Total runtime: 25114.486 ms
>> (36 rows)
>>
>> Time: 25122.948 ms
>>
>> no_merge plan:
>>
>> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>>  Hash Left Join  (cost=10355.63..934190.29 rows=224988 width=56) (actual
>> time=33.522..273.552 rows=9189 loops=1)
>>   Hash Cond: (_accrg7175_r._accountdtrref =
>> _acc7_extdim7144_tedacc1._acc7_idrref)
>>   ->  Hash Left Join  (cost=10338.23..930966.57 rows=153030 width=56)
>> (actual time=33.274..268.824 rows=9189 loops=1)
>>         Hash Cond: (_accrg7175_r._accountdtrref =
>> _acc7_extdim7144_tedacc2._acc7_idrref)
>>         ->  Nested Loop Left Join  (cost=10322.30..928626.34 rows=153030
>> width=56) (actual time=33.027..263.427 rows=9189 loops=1)
>>               Join Filter: ((_accrged7200_ted3._period =
>> _accrg7175_r._period) AND (_accrged7200_ted3._kindrref =
>> _acc7_extdim7144_tedacc3._dimkindrref))
>>               ->  Hash Left Join  (cost=10322.30..54166.12 rows=153030
>> width=83) (actual time=32.965..146.957 rows=9189 loops=1)
>>                     Hash Cond: (_accrg7175_r._accountdtrref =
>> _acc7_extdim7144_tedacc3._acc7_idrref)
>>                     ->  Hash Join  (cost=10308.08..52844.15 rows=153030
>> width=63) (actual time=32.790..143.699 rows=9189 loops=1)
>>                           Hash Cond: (_accrg7175_r._accountdtrref =
>> tt2._reffieldrref)
>>                           ->  Bitmap Heap Scan on _accrg7175 _accrg7175_r
>>  (cost=10305.89..51535.65 rows=237384 width=63) (actual time=32.541..105.359
>> 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
>>                                 ->  Bitmap Index Scan on
>> _accntr7175_byperiod_trn  (cost=0.00..10246.54 rows=237384 width=0) (actual
>> time=31.301..31.301 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.88..1.88 rows=25 width=17)
>> (actual time=0.049..0.049 rows=25 loops=1)
>>                                 ->  HashAggregate  (cost=1.62..1.88 rows=25
>> width=17) (actual time=0.035..0.039 rows=25 loops=1)
>>                                       ->  Seq Scan on tt2  (cost=0.00..1.50
>> rows=50 width=17) (actual time=0.009..0.012 rows=50 loops=1)
>>                     ->  Hash  (cost=13.64..13.64 rows=47 width=40) (actual
>> time=0.154..0.154 rows=47 loops=1)
>>                           ->  Seq Scan on _acc7_extdim7144
>> _acc7_extdim7144_tedacc3  (cost=0.00..13.64 rows=47 width=40) (actual
>> time=0.015..0.137 rows=47 loops=1)
>>                                 Filter: (_lineno = 3::numeric)
>>               ->  Index Scan using _accntr7200_byrecorder_rnn on
>> _accrged7200 _accrged7200_ted3  (cost=0.00..5.69 rows=1 width=63) (actual
>> time=0.010..0.011 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=20) (actual
>> time=0.236..0.236 rows=183 loops=1)
>>               ->  Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc2
>>  (cost=0.00..13.64 rows=183 width=20) (actual time=0.006..0.162 rows=183
>> loops=1)
>>                     Filter: (_lineno = 2::numeric)
>>   ->  Hash  (cost=13.64..13.64 rows=301 width=20) (actual time=0.236..0.236
>> rows=301 loops=1)
>>         ->  Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc1
>>  (cost=0.00..13.64 rows=301 width=20) (actual time=0.006..0.148 rows=301
>> loops=1)
>>               Filter: (_lineno = 1::numeric)
>>  Total runtime: 274.858 ms
>> (30 rows)
>>
>> Time: 281.339 ms
>>
>>
>>
>>        Regards,
>>                Oleg
>> _____________________________________________________________
>> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
>> Sternberg Astronomical Institute, Moscow University, Russia
>> Internet: oleg(at)sai(dot)msu(dot)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> writes:
> 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.

The "good" plan seems to be fast mainly because of heavily cached inner
indexscans. If that's the normal operating state for this database, you
should try reducing random_page_cost.

Also, as Pavel noted, the sub-join size estimates aren't very good, and
those overestimates are discouraging it from using inner-indexscan
nestloops. I'm not sure how much it would help to increase the
statistics targets, but that would be worth trying.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I'm not sure how much it would help to increase the statistics
> targets, but that would be worth trying.

I notice that the scan rowcount estimates are very accurate, there's
that one hash join result that's way off, though.

What's up with the sort of _accrged7200 (in the slower plan) taking
in 3.5 million rows and putting out 1 row? There's something there
I'm not understanding.

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> What's up with the sort of _accrged7200 (in the slower plan) taking
> in 3.5 million rows and putting out 1 row? There's something there
> I'm not understanding.

It's under a merge join, so what probably happened is that the first
row from that side had a larger key than any row from the other side.
A mergejoin will never bother to look at the remaining rows in such
a case.

regards, tom lane

On Thu, 15 Apr 2010, Kevin Grittner wrote:

> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> I'm not sure how much it would help to increase the statistics
>> targets, but that would be worth trying.

Setting statistics to 1000 helps for that particular reduced query, but
full query (attached) is out of luck.

>
> I notice that the scan rowcount estimates are very accurate, there's
> that one hash join result that's way off, though.
>
> What's up with the sort of _accrged7200 (in the slower plan) taking
> in 3.5 million rows and putting out 1 row? There's something there
> I'm not understanding.
>
> -Kevin
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

On Thu, 15 Apr 2010, Tom Lane wrote:

> Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> writes:
>> 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.
>
> The "good" plan seems to be fast mainly because of heavily cached inner
> indexscans. If that's the normal operating state for this database, you
> should try reducing random_page_cost.

Hmm, reducing random_page_cost to 3 helps, now all plans are the same.

>
> Also, as Pavel noted, the sub-join size estimates aren't very good, and
> those overestimates are discouraging it from using inner-indexscan
> nestloops. I'm not sure how much it would help to increase the
> statistics targets, but that would be worth trying.

Yes, setting statistics to 1000 helped for that paticular query (reduced by me),
but full query still chooses wrong plan with merge join. As you say before,
random_page_cost=3 helped.

I'm wondering if postgres could recognize such case (heavily cached inner
indexscans).

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

Sorry, I used random_page_cost=2, while random_page_cost=3 didn't help.

Oleg
On Fri, 16 Apr 2010, Oleg Bartunov wrote:

> On Thu, 15 Apr 2010, Tom Lane wrote:
>
>> Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> writes:
>>> 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.
>>
>> The "good" plan seems to be fast mainly because of heavily cached inner
>> indexscans. If that's the normal operating state for this database, you
>> should try reducing random_page_cost.
>
> Hmm, reducing random_page_cost to 3 helps, now all plans are the same.
>
>>
>> Also, as Pavel noted, the sub-join size estimates aren't very good, and
>> those overestimates are discouraging it from using inner-indexscan
>> nestloops. I'm not sure how much it would help to increase the
>> statistics targets, but that would be worth trying.
>
> Yes, setting statistics to 1000 helped for that paticular query (reduced by
> me),
> but full query still chooses wrong plan with merge join. As you say before,
> random_page_cost=3 helped.
>
> I'm wondering if postgres could recognize such case (heavily cached inner
> indexscans).
>
>
> Regards,
> Oleg
> _____________________________________________________________
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru)
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83