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=w=M1Jy+VdpWCrnOSbzwL9ow3GQA@mail.gmail.com Whole thread Raw |
| In response to | Performance of NOT IN and <> with PG 9.0.4 (Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>) |
| Responses |
Re: Performance of NOT IN and <> with PG 9.0.4
(Robert Haas <robertmhaas@gmail.com>)
|
| List | pgsql-sql |
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@gmail.com>
On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic<jasmin.dizdarevic@gmail.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?
--