That's strange... If I comment out these rows --sum(coalesce(e.num_wert,0)), --sum(coalesce(d.num_wert,0)) in the given statement, it works fine with enable_material = 'on'. I didn't change any join.
other settings are unchanged. HashAggregate (cost=589873.86..593205.21 rows=12114 width=47) (actual time=3419.518..3420.525 rows=647 loops=1) -> Merge Left Join (cost=554245.55..587451.24 rows=121131 width=47) (actual time=1755.414..3088.434 rows=122639 loops=1) Merge Cond: (kd.kundnr = mis.facts.kundnr) Join Filter: (kd.datum = mis.facts.datum) -> Merge Left Join (cost=365183.34..367094.17 rows=121131 width=48) (actual time=1314.365..1826.776 rows=122639 loops=1) Merge Cond: (kd.kundnr = mis.facts.kundnr) Join Filter: (kd.datum = mis.facts.datum) -> Merge Left Join (cost=178801.36..179717.71 rows=121131 width=39) (actual time=1013.092..1409.786 rows=122639 loops=1) Merge Cond: (kd.kundnr = mis.facts.kundnr) Join Filter: (kd.datum = mis.facts.datum) -> Merge Left Join (cost=118647.16..119256.75 rows=121131 width=30) (actual time=802.493..1126.694 rows=122639 loops=1) Merge Cond: (kd.kundnr = mis.facts.kundnr) Join Filter: (kd.datum = mis.facts.datum) -> Sort (cost=58492.96..58795.79 rows=121131 width=21) (actual time=585.242..789.183 rows=122639 loops=1) Sort Key: kd.kundnr Sort Method: quicksort Memory: 12654kB -> Bitmap Heap Scan on kunde kd (cost=2807.60..48265.74 rows=121131 width=21) (actual time=35.392..116.865 rows=122639 loops=1) Recheck Cond: (datum = '2011-03-31'::date) -> Bitmap Index Scan on kunde_n_i0 (cost=0.00..2777.32 rows=121131 width=0) (actual time=34.166..34.166 rows=122639 loops=1) Index Cond: (datum = '2011-03-31'::date) -> Sort (cost=60154.20..60154.79 rows=234 width=23) (actual time=217.233..217.557 rows=1064 loops=1) Sort Key: mis.facts.kundnr Sort Method: quicksort Memory: 132kB -> Index Scan using facts_i0 on facts (cost=0.00..60144.99 rows=234 width=23) (actual time=0.397..216.340 rows=1064 loops=1) Index Cond: ((datum = '2011-03-31'::date) AND ((gruppe)::text = 'AKTIV'::text) AND ((produkt)::text = 'GESAMT'::text) AND ((eigenschaft)::text = 'EWB'::text)) -> Sort (cost=60154.20..60154.79 rows=234 width=23) (actual time=210.586..210.705 rows=321 loops=1) Sort Key: mis.facts.kundnr Sort Method: quicksort Memory: 50kB -> Index Scan using facts_i0 on facts (cost=0.00..60144.99 rows=234 width=23) (actual time=0.362..210.277 rows=321 loops=1) Index Cond: ((datum = '2011-03-31'::date) AND ((gruppe)::text = 'AKTIV'::text) AND ((produkt)::text = 'GESAMT'::text) AND ((eigenschaft)::text = 'KONSORTIAL'::text)) -> Sort (cost=186381.98..186484.76 rows=41115 width=23) (actual time=301.256..322.731 rows=18906 loops=1) Sort Key: mis.facts.kundnr Sort Method: quicksort Memory: 2246kB -> Bitmap Heap Scan on facts (cost=59334.37..183231.05 rows=41115 width=23) (actual time=226.500..251.622 rows=18906 loops=1) 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) (actual time=223.969..223.969 rows=18906 loops=1) Index Cond: ((datum = '2011-03-31'::date) AND ((gruppe)::text = 'AKTIV'::text) AND ((produkt)::text = 'GESAMT'::text) AND ((eigenschaft)::text = 'VOLUMEN'::text)) -> Sort (cost=189062.21..189167.62 rows=42162 width=23) (actual time=440.927..562.630 rows=48484 loops=1) Sort Key: mis.facts.kundnr Sort Method: quicksort Memory: 5324kB -> Bitmap Heap Scan on facts (cost=59334.63..185823.40 rows=42162 width=23) (actual time=249.059..293.248 rows=48484 loops=1) 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) (actual time=246.394..246.394 rows=48484 loops=1) Index Cond: ((datum = '2011-03-31'::date) AND ((gruppe)::text = 'PASSIV'::text) AND ((produkt)::text = 'GESAMT'::text) AND ((eigenschaft)::text = 'VOLUMEN'::text)) Total runtime: 3421.046 ms 2011/5/24 Robert Haas <robertmh...@gmail.com> > On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic > <jasmin.dizdare...@gmail.com> wrote: > > enable_material = off > > Is there any chance you can reproduce this with a simpler test case > that doesn't involve quite so many joins? > > It looks to me like shutting off enable_material is saving you mostly > by accident here. There's only one materialize node in the whole > plan. > > And just incidentally, do you have any of the other enable_* settings > turned off? > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >