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

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

> On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic
> <jasmin(dot)dizdarevic(at)gmail(dot)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
>