Re: Very ineffective plan with merge join - Mailing list pgsql-hackers
From | Pavel Stehule |
---|---|
Subject | Re: Very ineffective plan with merge join |
Date | |
Msg-id | x2s162867791004151340i8b3718fj1434f0e7f92f7c3e@mail.gmail.com Whole thread Raw |
In response to | Re: Very ineffective plan with merge join (Oleg Bartunov <oleg@sai.msu.su>) |
Responses |
Re: Very ineffective plan with merge join
(Oleg Bartunov <oleg@sai.msu.su>)
|
List | pgsql-hackers |
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 >
pgsql-hackers by date: