Re: [SQL] Performance of NOT IN and with PG 9.0.4
On Tue, May 24, 2011 at 5:10 PM, Jasmin Dizdarevic jasmin.dizdare...@gmail.com wrote: 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. That's not that strange. The planner thinks that the cost of the plan you're getting with enable_material=on is 729721.34, and the cost of the plan you're getting with enable_material=off is 727904.40, or approximately an 0.2% difference. Any little change you make to anything in the system, or just random changes in your statistics, could cause the plans to bounce back and forth between those two. The real question is why the planner thinks those two cost about the same, when in reality one of them is way faster than the other. You might want to look through the EXPLAIN ANALYZE output and try to figure out which part of the plan is being mis-estimated. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance of NOT IN and with PG 9.0.4
On Tue, May 24, 2011 at 7:45 AM, Jasmin Dizdarevic jasmin.dizdare...@gmail.com wrote: 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. Well, it makes me glad I insisted we add enable_material. But I can't really tell from this output what is happening. Can we see the EXPLAIN ANALYZE output on 9.0, with and without enable_material? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance of NOT IN and with PG 9.0.4
On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic jasmin.dizdare...@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? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance of NOT IN and with PG 9.0.4
Robert Haas robertmh...@gmail.com writes: On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic jasmin.dizdare...@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? I didn't stop to count, but are there enough that join_collapse_limit or from_collapse_limit could be in play? regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Performance of NOT IN and with PG 9.0.4
As I've understood the docs those 2 limits should not take effect, because the performance is going down when adding two aggregated columns, but only when enable_material is on. 2011/5/25 Tom Lane t...@sss.pgh.pa.us Robert Haas robertmh...@gmail.com writes: On Tue, May 24, 2011 at 3:32 PM, Jasmin Dizdarevic jasmin.dizdare...@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? I didn't stop to count, but are there enough that join_collapse_limit or from_collapse_limit could be in play? regards, tom lane
Re: [SQL] 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:
Re: [SQL] 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.dizdare...@gmail.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
Re: [SQL] 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 =
Re: [SQL] Performance of NOT IN and with PG 9.0.4
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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
