Re: Performance of NOT IN and <> with PG 9.0.4
От | Jasmin Dizdarevic |
---|---|
Тема | Re: Performance of NOT IN and <> with PG 9.0.4 |
Дата | |
Msg-id | BANLkTinp_jkY5mpruP8JkTekpDKMkjovyQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Performance of NOT IN and <> with PG 9.0.4 (Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>) |
Ответы |
Re: Performance of NOT IN and <> with PG 9.0.4
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: