Re: Very ineffective plan with merge join - Mailing list pgsql-hackers

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