pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance of NOT IN and <> with PG 9.0.4
10+ messages / 4 participants
[nested] [flat]
* Performance of NOT IN and <> with PG 9.0.4
@ 2011-05-16 10:05 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-16 11:06 ` Re: Performance of NOT IN and <> with PG 9.0.4 Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 1 reply; 10+ messages in thread
From: Jasmin Dizdarevic @ 2011-05-16 10:05 UTC (permalink / raw)
To: pgsql-sql
Hi,
is there a reason why Not IN-performance is so poor in 9.0.4 compared to
8.4?
Ty
Jasmin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance of NOT IN and <> with PG 9.0.4
2011-05-16 10:05 Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
@ 2011-05-16 11:06 ` Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Craig Ringer @ 2011-05-16 11:06 UTC (permalink / raw)
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance of NOT IN and <> with PG 9.0.4
2011-05-16 10:05 Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-16 11:06 ` Re: Performance of NOT IN and <> with PG 9.0.4 Craig Ringer <craig@postnewspapers.com.au>
@ 2011-05-24 10:49 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 11:45 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jasmin Dizdarevic @ 2011-05-24 10:49 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; pgsql-sql
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@postnewspapers.com.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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance of NOT IN and <> with PG 9.0.4
2011-05-16 10:05 Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-16 11:06 ` Re: Performance of NOT IN and <> with PG 9.0.4 Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
@ 2011-05-24 11:45 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 19:14 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jasmin Dizdarevic @ 2011-05-24 11:45 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; pgsql-sql; +Cc: robertmhaas@gmail.com
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.dizdarevic@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 (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@postnewspapers.com.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
>>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance of NOT IN and <> with PG 9.0.4
2011-05-16 10:05 Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-16 11:06 ` Re: Performance of NOT IN and <> with PG 9.0.4 Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 11:45 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
@ 2011-05-24 19:14 ` Robert Haas <robertmhaas@gmail.com>
2011-05-24 19:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Robert Haas @ 2011-05-24 19:14 UTC (permalink / raw)
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>; +Cc: Craig Ringer <craig@postnewspapers.com.au>; pgsql-sql
On Tue, May 24, 2011 at 7:45 AM, Jasmin Dizdarevic
<jasmin.dizdarevic@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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance of NOT IN and <> with PG 9.0.4
2011-05-16 10:05 Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-16 11:06 ` Re: Performance of NOT IN and <> with PG 9.0.4 Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 11:45 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 19:14 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
@ 2011-05-24 19:49 ` Robert Haas <robertmhaas@gmail.com>
2011-05-24 21:10 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-25 14:13 ` Re: Performance of NOT IN and <> with PG 9.0.4 Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Robert Haas @ 2011-05-24 19:49 UTC (permalink / raw)
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>; +Cc: pgsql-sql
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?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance of NOT IN and <> with PG 9.0.4
2011-05-16 10:05 Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-16 11:06 ` Re: Performance of NOT IN and <> with PG 9.0.4 Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 11:45 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 19:14 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
2011-05-24 19:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
@ 2011-05-24 21:10 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-25 19:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Jasmin Dizdarevic @ 2011-05-24 21:10 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; 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?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance of NOT IN and <> with PG 9.0.4
2011-05-16 10:05 Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-16 11:06 ` Re: Performance of NOT IN and <> with PG 9.0.4 Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 11:45 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 19:14 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
2011-05-24 19:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
2011-05-24 21:10 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
@ 2011-05-25 19:49 ` Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Robert Haas @ 2011-05-25 19:49 UTC (permalink / raw)
To: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>; +Cc: pgsql-sql
On Tue, May 24, 2011 at 5:10 PM, Jasmin Dizdarevic
<jasmin.dizdarevic@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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance of NOT IN and <> with PG 9.0.4
2011-05-16 10:05 Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-16 11:06 ` Re: Performance of NOT IN and <> with PG 9.0.4 Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 11:45 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 19:14 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
2011-05-24 19:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
@ 2011-05-25 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-25 15:23 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2011-05-25 14:13 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>; pgsql-sql
Robert Haas <robertmhaas@gmail.com> writes:
> 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?
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Performance of NOT IN and <> with PG 9.0.4
2011-05-16 10:05 Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-16 11:06 ` Re: Performance of NOT IN and <> with PG 9.0.4 Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 11:45 ` Re: Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 19:14 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
2011-05-24 19:49 ` Re: Performance of NOT IN and <> with PG 9.0.4 Robert Haas <robertmhaas@gmail.com>
2011-05-25 14:13 ` Re: Performance of NOT IN and <> with PG 9.0.4 Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-05-25 15:23 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jasmin Dizdarevic @ 2011-05-25 15:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; pgsql-sql
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 <tgl@sss.pgh.pa.us>
> Robert Haas <robertmhaas@gmail.com> writes:
> > 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?
>
> 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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2011-05-25 19:49 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-16 10:05 Performance of NOT IN and <> with PG 9.0.4 Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-16 11:06 ` Craig Ringer <craig@postnewspapers.com.au>
2011-05-24 10:49 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 11:45 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-24 19:14 ` Robert Haas <robertmhaas@gmail.com>
2011-05-24 19:49 ` Robert Haas <robertmhaas@gmail.com>
2011-05-24 21:10 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
2011-05-25 19:49 ` Robert Haas <robertmhaas@gmail.com>
2011-05-25 14:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-05-25 15:23 ` Jasmin Dizdarevic <jasmin.dizdarevic@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox