Re: Very ineffective plan with merge join

Hello

there is significant problem in statistics I think,

Regards
Pavel Stehule

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