Hi, found the problem.
238 sec. with set enable_material = 'on' 4(!) sec. with set enable_material = 'off' @Robert Haas: I thought it would be interesting to you, because you've committed a patch regarding materialization for 9.0. If you like to investigate this further, I can provide you more details. regards Jasmin 2011/5/24 Jasmin Dizdarevic <jasmin.dizdare...@gmail.com> > Hi, > > now I have an example and a query plan for 8.4 and 9.0.4. See the > differences! Performance with 9 is horrible. > I've eliminated the NOT-IN-Statements hoping it would be better, but this > had no effect. > > Statement: > > select kd.datum, kd.filiale, kd.kundart as segment, mis.shore(swiftlcd), > sum(coalesce(a.num_wert,0)) - sum(coalesce(ae.num_wert,0)) - > sum(coalesce(ak.num_wert,0)) as al, > sum(coalesce(s.num_wert,0)) as se, > sum(coalesce(e.num_wert,0)), > sum(coalesce(d.num_wert,0)) > from mis.kunde kd > left join mfacts a on kd.datum = a.datum and kd.kundnr = a.kundnr and > a.gruppe = 'AKTIV' and a.produkt = 'GESAMT' and a.eigenschaft = 'VOLUMEN' > left join mfacts ae on kd.datum = ae.datum and kd.kundnr = ae.kundnr and > ae.gruppe = 'AKTIV' and ae.produkt = 'GESAMT' and ae.eigenschaft = 'EWB' > left join mfacts ak on kd.datum = ak.datum and kd.kundnr = ak.kundnr and > ak.gruppe = 'AKTIV' and ak.produkt = 'GESAMT' and ak.eigenschaft = > 'KONSORTIAL' > left join mfacts s on kd.datum = s.datum and kd.kundnr = s.kundnr and > s.gruppe = 'PASSIV' and s.produkt = 'GESAMT' and s.eigenschaft = 'VOLUMEN' > left join mfacts e on kd.datum = e.datum and kd.kundnr = e.kundnr and > e.gruppe = 'DEPOT' and e.produkt = 'EIGEMI' and e.eigenschaft = 'NOM' > left join mfacts d on kd.datum = d.datum and kd.kundnr = d.kundnr and > d.gruppe = 'DEPOT' and d.produkt = 'GESAMT' and d.eigenschaft = 'VOLUMEN' > where kd.datum = '2011-03-31' > group by kd.datum, kd.filiale, kd.kundart, mis.shore(swiftlcd) > > > 8.4 > HashAggregate (cost=317.28..317.56 rows=1 width=59) > -> Nested Loop Left Join (cost=0.00..317.25 rows=1 width=59) > Join Filter: ((kd.datum = mis.facts.datum) AND (kd.kundnr = > mis.facts.kundnr)) > -> Nested Loop Left Join (cost=0.00..265.73 rows=1 width=61) > Join Filter: ((kd.datum = mis.facts.datum) AND (kd.kundnr = > mis.facts.kundnr)) > -> Nested Loop Left Join (cost=0.00..214.46 rows=1 > width=53) > Join Filter: ((kd.datum = mis.facts.datum) AND > (kd.kundnr = mis.facts.kundnr)) > -> Nested Loop Left Join (cost=0.00..163.18 rows=1 > width=45) > Join Filter: ((kd.datum = mis.facts.datum) AND > (kd.kundnr = mis.facts.kundnr)) > -> Nested Loop Left Join (cost=0.00..111.91 > rows=1 width=37) > Join Filter: ((kd.datum = mis.facts.datum) > AND (kd.kundnr = mis.facts.kundnr)) > -> Nested Loop Left Join > (cost=0.00..60.64 rows=1 width=29) > Join Filter: ((kd.datum = > mis.facts.datum) AND (kd.kundnr = mis.facts.kundnr)) > -> Index Scan using kunde_n_i0 on > kunde kd (cost=0.00..9.36 rows=1 width=21) > Index Cond: (datum = > '2011-03-31'::date) > -> Index Scan using facts_i0 on > facts (cost=0.00..51.26 rows=1 width=22) > Index Cond: ((mis.facts.datum = > '2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'DEPOT'::text) AND > ((mis.facts.produkt)::text = 'GESAMT'::text) AND > ((mis.facts.eigenschaft)::text = 'VOLUMEN'::text)) > -> Index Scan using facts_i0 on facts > (cost=0.00..51.26 rows=1 width=22) > Index Cond: ((mis.facts.datum = > '2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'DEPOT'::text) AND > ((mis.facts.produkt)::text = 'EIGEMI'::text) AND > ((mis.facts.eigenschaft)::text = 'NOM'::text)) > -> Index Scan using facts_i0 on facts > (cost=0.00..51.26 rows=1 width=22) > Index Cond: ((mis.facts.datum = > '2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'PASSIV'::text) AND > ((mis.facts.produkt)::text = 'GESAMT'::text) AND > ((mis.facts.eigenschaft)::text = 'VOLUMEN'::text)) > -> Index Scan using facts_i0 on facts > (cost=0.00..51.26 rows=1 width=22) > Index Cond: ((mis.facts.datum = > '2011-03-31'::date) AND ((mis.facts.gruppe)::text = 'AKTIV'::text) AND > ((mis.facts.produkt)::text = 'GESAMT'::text) AND > ((mis.facts.eigenschaft)::text = 'KONSORTIAL'::text)) > -> Index Scan using facts_i0 on facts (cost=0.00..51.26 > rows=1 width=22) > Index Cond: ((mis.facts.datum = '2011-03-31'::date) AND > ((mis.facts.gruppe)::text = 'AKTIV'::text) AND ((mis.facts.produkt)::text = > 'GESAMT'::text) AND ((mis.facts.eigenschaft)::text = 'EWB'::text)) > -> Index Scan using facts_i0 on facts (cost=0.00..51.26 rows=1 > width=22) > Index Cond: ((mis.facts.datum = '2011-03-31'::date) AND > ((mis.facts.gruppe)::text = 'AKTIV'::text) AND ((mis.facts.produkt)::text = > 'GESAMT'::text) AND ((mis.facts.eigenschaft)::text = 'VOLUMEN'::text)) > > > And 9 > > HashAggregate (cost=726329.42..729721.34 rows=12114 width=65) > -> Merge Left Join (cost=628275.12..723301.14 rows=121131 width=65) > Merge Cond: (kd.kundnr = mis.facts.kundnr) > Join Filter: (kd.datum = mis.facts.datum) > -> Merge Left Join (cost=568120.92..632557.43 rows=121131 > width=66) > Merge Cond: (kd.kundnr = mis.facts.kundnr) > Join Filter: (kd.datum = mis.facts.datum) > -> Nested Loop Left Join (cost=494091.35..558155.42 > rows=121131 width=57) > Join Filter: ((kd.datum = mis.facts.datum) AND > (kd.kundnr = mis.facts.kundnr)) > -> Merge Left Join (cost=494091.35..496707.53 > rows=121131 width=48) > Merge Cond: (kd.kundnr = mis.facts.kundnr) > Join Filter: (kd.datum = mis.facts.datum) > -> Merge Left Join (cost=305029.14..306633.21 > rows=121131 width=39) > Merge Cond: (kd.kundnr = mis.facts.kundnr) > Join Filter: (kd.datum = mis.facts.datum) > -> Merge Left Join > (cost=244874.94..246172.25 rows=121131 width=30) > Merge Cond: (kd.kundnr = > mis.facts.kundnr) > Join Filter: (kd.datum = > mis.facts.datum) > -> Sort (cost=58492.96..58795.79 > rows=121131 width=21) > Sort Key: kd.kundnr > -> Bitmap Heap Scan on kunde > kd (cost=2807.60..48265.74 rows=121131 width=21) > Recheck Cond: (datum = > '2011-03-31'::date) > -> Bitmap Index Scan on > kunde_n_i0 (cost=0.00..2777.32 rows=121131 width=0) > Index Cond: (datum > = '2011-03-31'::date) > -> Sort (cost=186381.98..186484.76 > rows=41115 width=23) > Sort Key: mis.facts.kundnr > -> Bitmap Heap Scan on facts > (cost=59334.37..183231.05 rows=41115 width=23) > Recheck Cond: ((datum = > '2011-03-31'::date) AND ((gruppe)::text = 'AKTIV'::text) AND > ((produkt)::text = 'GESAMT'::text) AND ((eigenschaft)::text = > 'VOLUMEN'::text)) > -> Bitmap Index Scan on > facts_i0 (cost=0.00..59324.09 rows=41115 width=0) > Index Cond: ((datum > = '2011-03-31'::date) AND ((gruppe)::text = 'AKTIV'::text) AND > ((produkt)::text = 'GESAMT'::text) AND ((eigenschaft)::text = > 'VOLUMEN'::text)) > -> Sort (cost=60154.20..60154.78 rows=234 > width=23) > Sort Key: mis.facts.kundnr > -> Index Scan using facts_i0 on > facts (cost=0.00..60144.99 rows=234 width=23) > Index Cond: ((datum = > '2011-03-31'::date) AND ((gruppe)::text = 'AKTIV'::text) AND > ((produkt)::text = 'GESAMT'::text) AND ((eigenschaft)::text = 'EWB'::text)) > -> Sort (cost=189062.21..189167.62 rows=42162 > width=23) > Sort Key: mis.facts.kundnr > -> Bitmap Heap Scan on facts > (cost=59334.63..185823.40 rows=42162 width=23) > Recheck Cond: ((datum = > '2011-03-31'::date) AND ((gruppe)::text = 'PASSIV'::text) AND > ((produkt)::text = 'GESAMT'::text) AND ((eigenschaft)::text = > 'VOLUMEN'::text)) > -> Bitmap Index Scan on facts_i0 > (cost=0.00..59324.09 rows=42162 width=0) > Index Cond: ((datum = > '2011-03-31'::date) AND ((gruppe)::text = 'PASSIV'::text) AND > ((produkt)::text = 'GESAMT'::text) AND ((eigenschaft)::text = > 'VOLUMEN'::text)) > -> Materialize (cost=0.00..59328.10 rows=1 width=23) > -> Index Scan using facts_i0 on facts > (cost=0.00..59328.10 rows=1 width=23) > Index Cond: ((datum = '2011-03-31'::date) > AND ((gruppe)::text = 'DEPOT'::text) AND ((produkt)::text = 'EIGEMI'::text) > AND ((eigenschaft)::text = 'NOM'::text)) > -> Sort (cost=74029.57..74039.91 rows=4138 width=23) > Sort Key: mis.facts.kundnr > -> Index Scan using facts_i0 on facts > (cost=0.00..73780.98 rows=4138 width=23) > Index Cond: ((datum = '2011-03-31'::date) AND > ((gruppe)::text = 'DEPOT'::text) AND ((produkt)::text = 'GESAMT'::text) AND > ((eigenschaft)::text = 'VOLUMEN'::text)) > -> Sort (cost=60154.20..60154.78 rows=234 width=23) > Sort Key: mis.facts.kundnr > -> Index Scan using facts_i0 on facts (cost=0.00..60144.99 > rows=234 width=23) > Index Cond: ((datum = '2011-03-31'::date) AND > ((gruppe)::text = 'AKTIV'::text) AND ((produkt)::text = 'GESAMT'::text) AND > ((eigenschaft)::text = 'KONSORTIAL'::text)) > > > Thanks > > 2011/5/16 Craig Ringer <cr...@postnewspapers.com.au> > >> On 05/16/2011 06:05 PM, Jasmin Dizdarevic wrote: >> >>> Hi, >>> >>> is there a reason why Not IN-performance is so poor in 9.0.4 compared to >>> 8.4? >>> >> >> Example queries? >> >> EXPLAIN ANALYZE output? >> >> http://wiki.postgresql.org/wiki/SlowQueryQuestions >> >> -- >> Craig Ringer >> > >