Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 8F8BBB5DBF8 for ; Tue, 24 May 2011 07:49:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 37040-06-3 for ; Tue, 24 May 2011 10:49:47 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yi0-f46.google.com (mail-yi0-f46.google.com [209.85.218.46]) by mail.postgresql.org (Postfix) with ESMTP id 08D42B5DCCB for ; Tue, 24 May 2011 07:49:34 -0300 (ADT) Received: by yia27 with SMTP id 27so2498696yia.19 for ; Tue, 24 May 2011 03:49:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:content-type; bh=DTq9jK3fjTp+DuIyEHybyul9AcTq6FAJADraxgck8X0=; b=g5ufw9Uv8vGFWRn8xzho4hVU4Bx8YxfeRVjaOln1OUpLG3JvyqV6T8uF3UBpJEC6Yz xarqmgErZ53Bs4jmYCPlJgIcUtH2lk6A3KJKcQccGkbKarVOmt2ueAGdXacMDVOoG/0/ mG1i8jVcv2PUtAkCgYt4X3bsUad9KOpk0WIT0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; b=ka50Q+BGad3dYGxnGtFywQElS6r5aAhAyfRYiGbHlipDnW1JmBSjloaXWK71fhSjXV bsJcrEQ8TzWjpo8O8CuE9nSY2SfZR4YkgyUeiCLaUPHFp9w+Oc8n7vAOY992U/RPoM2c zYOUGDOkD2D/+XlSobG2h9hfvATUJVqV9Hp2w= MIME-Version: 1.0 Received: by 10.236.201.166 with SMTP id b26mr4470782yho.401.1306234174719; Tue, 24 May 2011 03:49:34 -0700 (PDT) Received: by 10.236.110.129 with HTTP; Tue, 24 May 2011 03:49:34 -0700 (PDT) In-Reply-To: <4DD10533.7050904@postnewspapers.com.au> References: <4DD10533.7050904@postnewspapers.com.au> Date: Tue, 24 May 2011 12:49:34 +0200 Message-ID: Subject: Re: Performance of NOT IN and <> with PG 9.0.4 From: Jasmin Dizdarevic To: Craig Ringer , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=20cf305641effa46a404a4035799 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.897 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201105/143 X-Sequence-Number: 35427 --20cf305641effa46a404a4035799 Content-Type: text/plain; charset=ISO-8859-1 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 > 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 > --20cf305641effa46a404a4035799 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi,=A0

now I have an example and a query plan for 8.4 an= d 9.0.4. See the differences! Performance with 9 is horrible.
I&#= 39;ve eliminated the NOT-IN-Statements hoping it would be better, but this = had no effect.

Statement:

select kd.datu= m, kd.filiale, kd.kundart as segment, mis.shore(swiftlcd),=A0
sum= (coalesce(a.num_wert,0)) - sum(coalesce(ae.num_wert,0)) - sum(coalesce(ak.n= um_wert,0)) as al,=A0
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 =3D a.datum and kd.kundnr =3D a.kundnr and a.gru= ppe =3D 'AKTIV' and a.produkt =3D 'GESAMT' and a.eigenschaf= t =3D 'VOLUMEN'
left join mfacts ae on kd.datum =3D ae.datum and kd.kundnr =3D ae.kund= nr and ae.gruppe =3D 'AKTIV' and ae.produkt =3D 'GESAMT' an= d ae.eigenschaft =3D 'EWB'
left join mfacts ak on kd.datu= m =3D ak.datum and kd.kundnr =3D ak.kundnr and ak.gruppe =3D 'AKTIV'= ; and ak.produkt =3D 'GESAMT' and ak.eigenschaft =3D 'KONSORTIA= L'
left join mfacts s on kd.datum =3D s.datum and kd.kundnr =3D s.kundnr = and s.gruppe =3D 'PASSIV' and s.produkt =3D 'GESAMT' and s.= eigenschaft =3D 'VOLUMEN'
left join mfacts e on kd.datum = =3D e.datum and kd.kundnr =3D e.kundnr and e.gruppe =3D 'DEPOT' and= e.produkt =3D 'EIGEMI' and e.eigenschaft =3D 'NOM'
left join mfacts d on kd.datum =3D d.datum and kd.kundnr =3D d.kundnr = and d.gruppe =3D 'DEPOT' and d.produkt =3D 'GESAMT' and d.e= igenschaft =3D 'VOLUMEN'
where kd.datum =3D '2011-03-31'
group by kd.datum, k= d.filiale, kd.kundart, mis.shore(swiftlcd)


8.4
HashAggregate =A0(cost=3D317.28..317.56 rows=3D1= width=3D59)
=A0 -> =A0Nested Loop Left Join =A0(cost=3D0.00..317.25 rows=3D1 wi= dth=3D59)
=A0 =A0 =A0 =A0 Join Filter: ((kd.datum =3D mis.facts.d= atum) AND (kd.kundnr =3D mis.facts.kundnr))
=A0 =A0 =A0 =A0 ->= =A0Nested Loop Left Join =A0(cost=3D0.00..265.73 rows=3D1 width=3D61)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 Join Filter: ((kd.datum =3D mis.facts.datu= m) AND (kd.kundnr =3D mis.facts.kundnr))
=A0 =A0 =A0 =A0 =A0 =A0 = =A0 -> =A0Nested Loop Left Join =A0(cost=3D0.00..214.46 rows=3D1 width= =3D53)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Join Filter: ((kd.= datum =3D mis.facts.datum) AND (kd.kundnr =3D mis.facts.kundnr))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Nested Loop Left Join= =A0(cost=3D0.00..163.18 rows=3D1 width=3D45)
=A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Join Filter: ((kd.datum =3D mis.facts.datu= m) AND (kd.kundnr =3D mis.facts.kundnr))
=A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Nested Loop Left Join =A0(cost=3D0.00.= .111.91 rows=3D1 width=3D37)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Join F= ilter: ((kd.datum =3D mis.facts.datum) AND (kd.kundnr =3D mis.facts.kundnr)= )
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= -> =A0Nested Loop Left Join =A0(cost=3D0.00..60.64 rows=3D1 width=3D29)=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 Join Filter: ((kd.datum =3D mis.facts.datum) AND (kd.kundnr =3D mis= .facts.kundnr))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using kunde_n_i0 on kunde k= d =A0(cost=3D0.00..9.36 rows=3D1 width=3D21)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 Index Cond: (datum =3D '2011-03-31'::date)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 -> =A0Index Scan using facts_i0 on facts =A0(cost=3D0.00..51.26 = rows=3D1 width=3D22)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 Index Cond: ((mis.facts.datum =3D '2011-03-31':= :date) AND ((mis.facts.gruppe)::text =3D 'DEPOT'::text) AND ((mis.f= acts.produkt)::text =3D 'GESAMT'::text) AND ((mis.facts.eigenschaft= )::text =3D 'VOLUMEN'::text))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> = =A0Index Scan using facts_i0 on facts =A0(cost=3D0.00..51.26 rows=3D1 width= =3D22)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 Index Cond: ((mis.facts.datum =3D '2011-03-31':= :date) AND ((mis.facts.gruppe)::text =3D 'DEPOT'::text) AND ((mis.f= acts.produkt)::text =3D 'EIGEMI'::text) AND ((mis.facts.eigenschaft= )::text =3D 'NOM'::text))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Sca= n using facts_i0 on facts =A0(cost=3D0.00..51.26 rows=3D1 width=3D22)
=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index = Cond: ((mis.facts.datum =3D '2011-03-31'::date) AND ((mis.facts.gru= ppe)::text =3D 'PASSIV'::text) AND ((mis.facts.produkt)::text =3D &= #39;GESAMT'::text) AND ((mis.facts.eigenschaft)::text =3D 'VOLUMEN&= #39;::text))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using fact= s_i0 on facts =A0(cost=3D0.00..51.26 rows=3D1 width=3D22)
=A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((mis.facts.datum = =3D '2011-03-31'::date) AND ((mis.facts.gruppe)::text =3D 'AKTI= V'::text) AND ((mis.facts.produkt)::text =3D 'GESAMT'::text) AN= D ((mis.facts.eigenschaft)::text =3D 'KONSORTIAL'::text))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using facts_i0 on fact= s =A0(cost=3D0.00..51.26 rows=3D1 width=3D22)
=A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 Index Cond: ((mis.facts.datum =3D '2011-03-31'= ::date) AND ((mis.facts.gruppe)::text =3D 'AKTIV'::text) AND ((mis.= facts.produkt)::text =3D 'GESAMT'::text) AND ((mis.facts.eigenschaf= t)::text =3D 'EWB'::text))
=A0 =A0 =A0 =A0 -> =A0Index Scan using facts_i0 on facts =A0(cost= =3D0.00..51.26 rows=3D1 width=3D22)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 I= ndex Cond: ((mis.facts.datum =3D '2011-03-31'::date) AND ((mis.fact= s.gruppe)::text =3D 'AKTIV'::text) AND ((mis.facts.produkt)::text = =3D 'GESAMT'::text) AND ((mis.facts.eigenschaft)::text =3D 'VOL= UMEN'::text))


And 9

HashAggregate =A0(cost=3D726329.42..729721.34 rows=3D12114 width=3D= 65)
=A0 -> =A0Merge Left Join =A0(cost=3D628275.12..723301.14 = rows=3D121131 width=3D65)
=A0 =A0 =A0 =A0 Merge Cond: (kd.kundnr =3D mis.facts.kundnr)
=A0 =A0 =A0 =A0 Join Filter: (kd.datum =3D mis.facts.datum)
=A0 = =A0 =A0 =A0 -> =A0Merge Left Join =A0(cost=3D568120.92..632557.43 rows= =3D121131 width=3D66)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 Merge Cond: (kd= .kundnr =3D mis.facts.kundnr)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 Join Filter: (kd.datum =3D mis.facts.datum= )
=A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Nested Loop Left Join =A0(= cost=3D494091.35..558155.42 rows=3D121131 width=3D57)
=A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 Join Filter: ((kd.datum =3D mis.facts.datum) A= ND (kd.kundnr =3D mis.facts.kundnr))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Merge Left Join =A0(c= ost=3D494091.35..496707.53 rows=3D121131 width=3D48)
=A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Merge Cond: (kd.kundnr =3D mis.fact= s.kundnr)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Joi= n Filter: (kd.datum =3D mis.facts.datum)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Merge Lef= t Join =A0(cost=3D305029.14..306633.21 rows=3D121131 width=3D39)
= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Merge Cond:= (kd.kundnr =3D mis.facts.kundnr)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Join Filter: (kd.datum =3D mis.facts.datum= )
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> = =A0Merge Left Join =A0(cost=3D244874.94..246172.25 rows=3D121131 width=3D30= )
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 Merge Cond: (kd.kundnr =3D mis.facts.kundnr)
=A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Join F= ilter: (kd.datum =3D mis.facts.datum)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 -> =A0Sort =A0(cost=3D58492.96..58795.79 rows=3D121131 width=3D2= 1)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 Sort Key: kd.kundnr
=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -&g= t; =A0Bitmap Heap Scan on kunde kd =A0(cost=3D2807.60..48265.74 rows=3D1211= 31 width=3D21)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Recheck Cond: (datum =3D '2011-03-31= 9;::date)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Bitmap Index Scan on = kunde_n_i0 =A0(cost=3D0.00..2777.32 rows=3D121131 width=3D0)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: (datum =3D '201= 1-03-31'::date)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Sort =A0(cost=3D186381.98..186484.= 76 rows=3D41115 width=3D23)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 Sort Key: mis.facts.kundnr
=A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -&g= t; =A0Bitmap Heap Scan on facts =A0(cost=3D59334.37..183231.05 rows=3D41115= width=3D23)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 Recheck Cond: ((datum =3D '2011-03-31'::da= te) AND ((gruppe)::text =3D 'AKTIV'::text) AND ((produkt)::text =3D= 'GESAMT'::text) AND ((eigenschaft)::text =3D 'VOLUMEN'::te= xt))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Bitmap Index Scan on facts_i0 =A0(= cost=3D0.00..59324.09 rows=3D41115 width=3D0)
=A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 Index Cond: ((datum =3D '2011-03-31'::date) AND= ((gruppe)::text =3D 'AKTIV'::text) AND ((produkt)::text =3D 'G= ESAMT'::text) AND ((eigenschaft)::text =3D 'VOLUMEN'::text))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> = =A0Sort =A0(cost=3D60154.20..60154.78 rows=3D234 width=3D23)
=A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Sor= t Key: mis.facts.kundnr
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using facts_i0 on f= acts =A0(cost=3D0.00..60144.99 rows=3D234 width=3D23)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 Index Cond: ((datum =3D '2011-03-31'::date) AND= ((gruppe)::text =3D 'AKTIV'::text) AND ((produkt)::text =3D 'G= ESAMT'::text) AND ((eigenschaft)::text =3D 'EWB'::text))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Sort =A0(= cost=3D189062.21..189167.62 rows=3D42162 width=3D23)
=A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Sort Key: mis.facts.kun= dnr
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 -> =A0Bitmap Heap Scan on facts =A0(cost=3D59334.63..185823.40 rows= =3D42162 width=3D23)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 Recheck Cond: ((datum =3D '2011-03-31'::date) AND ((gruppe)= ::text =3D 'PASSIV'::text) AND ((produkt)::text =3D 'GESAMT'= ;::text) AND ((eigenschaft)::text =3D 'VOLUMEN'::text))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 -> =A0Bitmap Index Scan on facts_i0 =A0(cost=3D0.00..59324.09 ro= ws=3D42162 width=3D0)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((datum =3D '2= 011-03-31'::date) AND ((gruppe)::text =3D 'PASSIV'::text) AND (= (produkt)::text =3D 'GESAMT'::text) AND ((eigenschaft)::text =3D &#= 39;VOLUMEN'::text))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Materialize =A0(cost= =3D0.00..59328.10 rows=3D1 width=3D23)
=A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using facts_i0 on facts =A0= (cost=3D0.00..59328.10 rows=3D1 width=3D23)
=A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((datum =3D '20= 11-03-31'::date) AND ((gruppe)::text =3D 'DEPOT'::text) AND ((p= rodukt)::text =3D 'EIGEMI'::text) AND ((eigenschaft)::text =3D '= ;NOM'::text))
=A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Sort =A0(cost=3D74029.57..74039.9= 1 rows=3D4138 width=3D23)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= Sort Key: mis.facts.kundnr
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 -> =A0Index Scan using facts_i0 on facts =A0(cost=3D0.00..73780.98 r= ows=3D4138 width=3D23)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((datu= m =3D '2011-03-31'::date) AND ((gruppe)::text =3D 'DEPOT'::= text) AND ((produkt)::text =3D 'GESAMT'::text) AND ((eigenschaft)::= text =3D 'VOLUMEN'::text))
=A0 =A0 =A0 =A0 -> =A0Sort =A0(cost=3D60154.20..60154.78 rows=3D234= width=3D23)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 Sort Key: mis.facts.kund= nr
=A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Index Scan using facts_i0= on facts =A0(cost=3D0.00..60144.99 rows=3D234 width=3D23)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((datum =3D '2= 011-03-31'::date) AND ((gruppe)::text =3D 'AKTIV'::text) AND ((= produkt)::text =3D 'GESAMT'::text) AND ((eigenschaft)::text =3D = 9;KONSORTIAL'::text))


Thanks

2011/5/16 Craig Ringer <craig@postnewspapers.com.au>
On 05/16/2011 06:05 PM, J= asmin 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

--20cf305641effa46a404a4035799--