Re: Performance of NOT IN and <> with PG 9.0.4 - Mailing list pgsql-sql
| From | Jasmin Dizdarevic |
|---|---|
| Subject | Re: Performance of NOT IN and <> with PG 9.0.4 |
| Date | |
| Msg-id | BANLkTi=GmO5NaTGb80e7LpX8mRcvPyKucg@mail.gmail.com Whole thread Raw |
| In response to | Re: Performance of NOT IN and <> with PG 9.0.4 (Craig Ringer <craig@postnewspapers.com.au>) |
| Responses |
Re: Performance of NOT IN and <> with PG 9.0.4
|
| List | pgsql-sql |
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