Re: Very ineffective plan with merge join

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