Re: Very ineffective plan with merge join

On Thu, 15 Apr 2010, Pavel Stehule wrote:

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

Ah, you're right !

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

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