Re: Performance of NOT IN and <> with PG 9.0.4

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(at)postnewspapers(dot)com(dot)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
>