Обсуждение: 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_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
Oleg Bartunov <oleg@sai.msu.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@sai.msu.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:
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashLeft Join (cost=762035.09..822357.84 rows=224988 width=56) (actual time=25007.488..25022.338 rows=9189 loops=1)
HashCond: (_accrg7175_r._accountdtrref = _acc7_extdim7144_tedacc1._acc7_idrref) -> Hash Left Join
(cost=762017.69..819134.13rows=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.89rows=153030 width=56) (actual time=25006.895..25012.218 rows=9189 loops=1)
MergeCond: ((_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)
(actualtime=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=153030width=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.15rows=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=237384width=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
withouttime zone)) -> Hash (cost=1.88..1.88 rows=25 width=17) (actual
time=0.077..0.077rows=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.50rows=50 width=17) (actual time=0.020..0.023 rows=50 loops=1) -> Hash
(cost=13.64..13.64rows=47 width=40) (actual time=0.200..0.200 rows=47 loops=1) -> Seq
Scanon _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=183width=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=301loops=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.486ms
(36 rows)
Time: 25122.948 ms
no_merge plan:
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashLeft 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.57rows=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.34rows=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)
(actualtime=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)
(actualtime=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=237384width=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
withouttime zone)) Filter: _active -> Bitmap Index
Scanon _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.88rows=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.137rows=47 loops=1) Filter: (_lineno = 3::numeric) ->
IndexScan using _accntr7200_byrecorder_rnn on _accrged7200 _accrged7200_ted3 (cost=0.00..5.69 rows=1 width=63) (actual
time=0.010..0.011rows=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.64rows=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.236rows=301 loops=1) -> Seq Scan on _acc7_extdim7144 _acc7_extdim7144_tedacc1 (cost=0.00..13.64
rows=301width=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@sai.msu.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@sai.msu.su>: > On Thu, 15 Apr 2010, Kevin Grittner wrote: > >> Oleg Bartunov <oleg@sai.msu.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@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
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@sai.msu.su>: >> On Thu, 15 Apr 2010, Kevin Grittner wrote: >> >>> Oleg Bartunov <oleg@sai.msu.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@sai.msu.su, http://www.sai.msu.su/~megera/ >> phone: +007(495)939-16-83, +007(495)939-23-83 >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers >> > 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
Oleg Bartunov <oleg@sai.msu.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@sss.pgh.pa.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.Grittner@wicourts.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@sss.pgh.pa.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@sai.msu.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@sai.msu.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@sai.msu.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@sai.msu.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@sai.msu.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@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83