Обсуждение: Performance of NOT IN and <> with PG 9.0.4
Hi,
is there a reason why Not IN-performance is so poor in 9.0.4 compared to 8.4?
Ty
Jasmin
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
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 <craig@postnewspapers.com.au>
On 05/16/2011 06:05 PM, Jasmin Dizdarevic wrote:Example queries?Hi,
is there a reason why Not IN-performance is so poor in 9.0.4 compared to
8.4?
EXPLAIN ANALYZE output?
http://wiki.postgresql.org/wiki/SlowQueryQuestions
--
Craig Ringer
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.dizdarevic@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 kdleft 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.4HashAggregate (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 9HashAggregate (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))Thanks2011/5/16 Craig Ringer <craig@postnewspapers.com.au>On 05/16/2011 06:05 PM, Jasmin Dizdarevic wrote:Example queries?Hi,
is there a reason why Not IN-performance is so poor in 9.0.4 compared to
8.4?
EXPLAIN ANALYZE output?
http://wiki.postgresql.org/wiki/SlowQueryQuestions
--
Craig Ringer
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 <robertmhaas@gmail.com>
On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic<jasmin.dizdarevic@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?
--
On Tue, May 24, 2011 at 7:45 AM, Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> wrote: > 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. Well, it makes me glad I insisted we add enable_material. But I can't really tell from this output what is happening. Can we see the EXPLAIN ANALYZE output on 9.0, with and without enable_material? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic <jasmin.dizdarevic@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
Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic
> <jasmin.dizdarevic@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?
I didn't stop to count, but are there enough that join_collapse_limit
or from_collapse_limit could be in play?
regards, tom lane
As I've understood the docs those 2 limits should not take effect, because the performance is going down when adding twoaggregated columns, but only when enable_material is on.<br /><br /><div class="gmail_quote">2011/5/25 Tom Lane <spandir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>></span><br /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div class="im">Robert Haas <<ahref="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>> writes:<br /> > On Tue, May 24, 2011 at 3:32 PM,Jasmin Dizdarevic<br /> > <<a href="mailto:jasmin.dizdarevic@gmail.com">jasmin.dizdarevic@gmail.com</a>> wrote:<br/> >> enable_material = off<br /><br /> > Is there any chance you can reproduce this with a simpler testcase<br /> > that doesn't involve quite so many joins?<br /><br /></div>I didn't stop to count, but are there enoughthat join_collapse_limit<br /> or from_collapse_limit could be in play?<br /><br /> regards,tom lane<br /></blockquote></div><br />
On Tue, May 24, 2011 at 5:10 PM, Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com> wrote: > 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. That's not that strange. The planner thinks that the cost of the plan you're getting with enable_material=on is 729721.34, and the cost of the plan you're getting with enable_material=off is 727904.40, or approximately an 0.2% difference. Any little change you make to anything in the system, or just random changes in your statistics, could cause the plans to bounce back and forth between those two. The real question is why the planner thinks those two cost about the same, when in reality one of them is way faster than the other. You might want to look through the EXPLAIN ANALYZE output and try to figure out which part of the plan is being mis-estimated. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company