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

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(dot)dizdarevic(at)gmail(dot)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 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
>>
>
>