Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WXcoe-0005ap-Fm for pgsql-performance@arkaria.postgresql.org; Tue, 08 Apr 2014 20:40:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WXcod-0008OD-QZ for pgsql-performance@arkaria.postgresql.org; Tue, 08 Apr 2014 20:40:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WXcob-0008Mu-6L for pgsql-performance@postgresql.org; Tue, 08 Apr 2014 20:40:17 +0000 Received: from nm31-vm1.bullet.mail.sg3.yahoo.com ([106.10.151.192]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WXcoW-0007Tc-Pe for pgsql-performance@postgresql.org; Tue, 08 Apr 2014 20:40:16 +0000 Received: from [106.10.166.116] by nm31.bullet.mail.sg3.yahoo.com with NNFMP; 08 Apr 2014 20:40:09 -0000 Received: from [106.10.151.203] by tm5.bullet.mail.sg3.yahoo.com with NNFMP; 08 Apr 2014 20:40:09 -0000 Received: from [127.0.0.1] by omp1015.mail.sg3.yahoo.com with NNFMP; 08 Apr 2014 20:40:09 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 314993.21288.bm@omp1015.mail.sg3.yahoo.com Received: (qmail 77580 invoked by uid 60001); 8 Apr 2014 20:40:09 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1396989609; bh=yPCgGQJw+MG/oxO8dHHLVDjQQOfuiW2hA7FH/7t2JQE=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=ukjIsJrAqwwFtLAHOIVhX6+WWyjZ//PrjzQgy1rjlG73DiQ3VTYHIMsiPA1INiEXEvSL8nDhZ4uD7SQ4mrIkdcix0Bnt03TVN2xlIaaiOZ9huQEKYdgAN5uYN4BocgVgR0k4Nq1k46O6kytA9BjaRmVP31bq3tBYaYymyR7hut0= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=wGylUOiF+cvwTnkT458zC4ZSgbkvRlYynuevtp55mWiM+gUkhJ/3cKblkBIkW4O2hRvUWv5tfH1u7h1kjTLyUweg8pBPyktRTvYM4+i4hLz1lp8dQqmYxx8AEoJjRcLlqWMW2COyiX5asqhEABsUajNdbdD3jzppZQ51xdA5UZ4=; X-YMail-OSG: UBv3uCUVM1nIo_wLmY.H5wXztdHDT3K06cBt2svV5FCUMEx INVTO.q0OhXIgWop5m2HDrA24osuBnc8_by4fYoOFqYs2fLA_FEL.towV9.a rVLau38cuDDy5i5YMkMMNfLxnhmd5UT9UWZPpJ4nd95X7.Niltd7aWbIuhso krjN7IJ3ckEPa4LtyDP3O2xXvk8wzenD08R0UuluEjN3goosMX3uLr9IzZaW gtzYL1gulJWmZMddseI.R6LcO_oMrFHJsvK8s72v3QC5nz_3UDrIH4ZAXt3E W80glUXXzGxq8BOTqILRMg.M7gTsRjfZmQYV.C.0Tzzvy_b4q6acPYDzSYzj EhFF6HBM.MMPuHbzmiYUh.uAB2Ji_Yb4eDEo1QTKkioXrUq6xcv5yc_KBPxU YaCgXm0B6p7CEffIXMYOXmPAIFZy_3BXbSkjab9iE5ufLyMTTLDVgyXOaBoy BFhKQbiX3Abwn1NDO6H0FOBYhtVUVbl7ygX2rEldEpwkLjfB4TbeiGP4vIb9 QHaCggnTxNxhU2OyuS8aMKWXFn6fCqo5vZT5tGhoh7lgd8Ni6w7WpUWis46O Op.x2eCSTIGSEE5caU6tLEyPiGECNLtiofoKDzrf5JALiXya_.pdhUhI.PAK oqv0n Received: from [49.205.13.57] by web193601.mail.sg3.yahoo.com via HTTP; Wed, 09 Apr 2014 04:40:09 SGT X-Rocket-MIMEInfo: 002.001, UkVQTEFDRSAtLSB3aGVyZSBzb3Nfc3RnX2FnZ3IudGlkX3N0ZyBpbiAoc2VsZWN0IGRpc3RpbmN0IGxlaHJfc3RnX2FiX3RpZCBmcm9tIGxlaHJfc3RnX2FiMmZiKQoKV0lUSCAtLcKgd2hlcmUgc29zX3N0Z19hZ2dyLnRpZF9zdGcgRVhJU1RTwqAoc2VsZWN0IGRpc3RpbmN0IGxlaHJfc3RnX2FiX3RpZCBmcm9tIGxlaHJfc3RnX2FiMmZiKQoKU2ltaWxhcmx5IG90aGVycyBhbHNvIGxpa2UgLS3CoGxlaHJfc3RnX2FiLnRpZCBub3QgaW4gKHNlbGVjdCBsZWhyX3N0Z19hYl90aWQgZnJvbSBsZWhyX3N0Z19hYjIBMAEBAQE- X-Mailer: YahooMailWebService/0.8.182.648 References: <9d3d69016b886789.5343c2eb@limbe.rz.uni-konstanz.de> Message-ID: <1396989609.60432.YahooMailNeo@web193601.mail.sg3.yahoo.com> Date: Wed, 9 Apr 2014 04:40:09 +0800 (SGT) From: Dhananjay Singh Reply-To: Dhananjay Singh Subject: Re: Nested loop issue To: Manoj Gadi , "pgsql-performance@postgresql.org" In-Reply-To: <9d3d69016b886789.5343c2eb@limbe.rz.uni-konstanz.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="2063643590-1735647066-1396989609=:60432" X-Pg-Spam-Score: -1.8 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --2063643590-1735647066-1396989609=:60432 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable REPLACE -- where sos_stg_aggr.tid_stg in (select distinct lehr_stg_ab_tid f= rom lehr_stg_ab2fb)=0A=0AWITH --=A0where sos_stg_aggr.tid_stg EXISTS=A0(sel= ect distinct lehr_stg_ab_tid from lehr_stg_ab2fb)=0A=0ASimilarly others als= o like --=A0lehr_stg_ab.tid not in (select lehr_stg_ab_tid from lehr_stg_ab= 2fb) with NOT EXISTS=0A=0A=0AThis should surely going to improve performanc= e depending on results from inner query.=0A=0ARegards=0ADhananjay=0AOpenSCG= =0A=0A=0AOn Tuesday, 8 April 2014 3:06 PM, Manoj Gadi wrote:=0A =0AHi All,=0A=0AI have been looking for a solution to a p= roblem where my query is executing for a long time because it is running in= to a nested loop problem.=0A=0AI have done explain analyze and it shows the= query taking a very long time due to nested loops.=0A=0AOn the DB side, th= ere are indices in place for all the required columns. By setting nested lo= op off there is a drastic increase in performance (from 40,000 ms to 600 ms= ) but I know this is not a right practice.=0A=0AMy postgres version is 9.3.= 2 on linux.=0A=0APlease find the link for the query plan below :=0A=0Ahttp:= //explain.depesz.com/s/l9o=0A=0A=0AAlso, find below the query that is being= executed.=0A=0ASELECT DISTINCT=0A=A0 "Sektion/Fachbereich"."parent",=0A= =A0 "Studienfach"."ltxt",=0A=A0 SUM(CASE=0A=A0 =A0 =A0 WHEN "Studiengang"= ."faktor" IS NOT NULL=0A=A0 =A0 =A0 AND "Studiengang"."faktor" >=3D 0 THEN = "Studiengang"."faktor" * "Studierende"."summe"=0A=A0 =A0 =A0 ELSE "Studiere= nde"."summe"=0A=A0 END)=0A=0AFROM (=0A=A0 SELECT sos_stg_aggr.tid_stg, so= s_stg_aggr.ca12_staat, sos_stg_aggr.geschlecht, sos_stg_aggr.alter, sos_stg= _aggr.hzbart, sos_stg_aggr.hmkfzkz, sos_stg_aggr.hmkfz, sos_stg_aggr.semkfz= kz, sos_stg_aggr.semkfz, sos_stg_aggr.hzbkfzkz, sos_stg_aggr.hzbkfz, sos_st= g_aggr.hrst, sos_stg_aggr.studiengang_nr, sos_stg_aggr.fach_nr, sos_stg_agg= r.fach_sem_zahl, sos_stg_aggr.sem_rueck_beur_ein, sos_stg_aggr.kz_rueck_beu= r_ein, sos_stg_aggr.klinsem, sos_stg_aggr.hssem, sos_stg_aggr.stuart, sos_s= tg_aggr.stutyp, sos_stg_aggr.stufrm, sos_stg_aggr.stichtag, sos_stg_aggr.su= mme, sos_stg_aggr.hzbart_int, sos_stg_aggr.matrikel_nr, sos_stg_aggr.ch27_g= rund_beurl, sos_stg_aggr.ch62_grund_exmatr, sos_stg_aggr.hzbnote, textcat(s= os_stg_aggr.studiengang_nr::text, sos_stg_aggr.fach_nr::text) AS koepfe_fae= lle=0A=A0 FROM sos_stg_aggr=0A=0A=A0 union all=0A=0A=A0 SELECT sos_stg_a= ggr.tid_stg, sos_stg_aggr.ca12_staat, sos_stg_aggr.geschlecht, sos_stg_aggr= .alter, sos_stg_aggr.hzbart, sos_stg_aggr.hmkfzkz, sos_stg_aggr.hmkfz, sos_= stg_aggr.semkfzkz, sos_stg_aggr.semkfz, sos_stg_aggr.hzbkfzkz, sos_stg_aggr= .hzbkfz, sos_stg_aggr.hrst, sos_stg_aggr.studiengang_nr, sos_stg_aggr.fach_= nr, sos_stg_aggr.fach_sem_zahl, sos_stg_aggr.sem_rueck_beur_ein, sos_stg_ag= gr.kz_rueck_beur_ein, sos_stg_aggr.klinsem, sos_stg_aggr.hssem, sos_stg_agg= r.stuart, sos_stg_aggr.stutyp, sos_stg_aggr.stufrm, sos_stg_aggr.stichtag, = sos_stg_aggr.summe, sos_stg_aggr.hzbart_int, sos_stg_aggr.matrikel_nr, sos_= stg_aggr.ch27_grund_beurl, sos_stg_aggr.ch62_grund_exmatr, sos_stg_aggr.hzb= note, '21' AS koepfe_faelle=0A=A0 FROM sos_stg_aggr=0A=A0 where sos_stg_a= ggr.tid_stg in (select distinct lehr_stg_ab_tid from lehr_stg_ab2fb)=0A=0A)= AS "Studierende"=0AINNER JOIN (=0A=A0 select astat::integer, trim(druck) = as druck from sos_k_status=0A=0A) AS "R=FCckmeldestatus"=0AON (=0A=A0 "Stu= dierende"."kz_rueck_beur_ein" =3D "R=FCckmeldestatus"."astat"=0A)=0AINNER J= OIN (=0A=A0 select tid, trim(name) as name from sos_stichtag=0A=0A) AS "St= ichtag"=0AON (=0A=A0 "Studierende"."stichtag" =3D "Stichtag"."tid"=0A)=0AI= NNER JOIN (=0A=A0 select abschluss, kz_fach, stg, pversion, regel, trim(te= xt) as text, fb, lehr, anteil, tid,null as faktor from lehr_stg_ab=0Awhere = lehr_stg_ab.tid not in (select lehr_stg_ab_tid from lehr_stg_ab2fb)=0A=0Aun= ion=0Aselect abschluss, kz_fach, stg, pversion, regel, trim(text) as text, = lehr_stg_ab2fb.fb, lehr, anteil, tid,faktor from lehr_stg_ab=0Ainner join l= ehr_stg_ab2fb=0Aon lehr_stg_ab2fb.lehr_stg_ab_tid =3D lehr_stg_ab.tid=0A=0A= ) AS "Studiengang"=0AON (=0A=A0 "Studierende"."tid_stg" =3D "Studiengang".= "tid"=0A)=0AINNER JOIN (=0A=A0 select astat, astfr, astgrp, fb, trim(ltxt)= as ltxt, stg from k_stg=0A=0A) AS "Studienfach"=0AON (=0A=A0 "Studiengang= "."stg" =3D "Studienfach"."stg"=0A)=0AAND (=0A=A0 "Studienfach"."ltxt" IS = NOT NULL=0A)=0AINNER JOIN (=0A=A0 select instnr, ch110_institut, btrim(dru= ck) as druck, btrim(parent) as parent from unikn_k_fb=0A=0A) AS "Sektion/Fa= chbereich"=0AON (=0A=A0 "Studiengang"."fb" =3D "Sektion/Fachbereich"."inst= nr"=0A)=0AINNER JOIN (=0A=A0 select apnr, trim(druck) as druck from cifx w= here key=3D613=0A=0A) AS "H=F6rerstatus"=0AON (=0A=A0 "Studierende"."hrst"= =3D "H=F6rerstatus"."apnr"=0A)=0AWHERE=0A(=0A=A0 "Sektion/Fachbereich"."d= ruck" =3D 'FB Biologie'=0A)=0AAND=0A(=0A=A0 (=0A=A0 =A0 =A0 "H=F6rerstatus= "."druck" =3D 'Haupth=F6rer/in'=0A=A0 =A0 =A0 AND "Stichtag"."name" =3D 'Am= tl. Statistik Land'=0A=A0 =A0 =A0 AND "R=FCckmeldestatus"."druck" IN ('Beur= laubung', 'Ersteinschreibung', 'Neueinschreibung', 'R=FCckmeldung')=0A=A0 = =A0 =A0 AND "Studierende"."sem_rueck_beur_ein" =3D 20132=0A=A0 )=0A)=0AGRO= UP BY=0A=A0 "Sektion/Fachbereich"."parent",=0A=A0 "Studienfach"."ltxt"=0A= =0A=0AAccording to my analysis, the where clause after the Union All is tak= ing a lot of time for execution.=0A=0AAny help with an alternative way to r= epresent the query or what the cause of issue would be very helpful.=0A=0A= =0AThanks in advance,=0AManoj=0A=0A=0A-- =0ASent via pgsql-performance mail= ing list (pgsql-performance@postgresql.org)=0ATo make changes to your subsc= ription:=0Ahttp://www.postgresql.org/mailpref/pgsql-performance --2063643590-1735647066-1396989609=:60432 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable
REPLACE --= where sos_stg_aggr.tid_stg in (select distinct lehr_stg_ab_tid from lehr_s= tg_ab2fb)
WITH -- where sos_stg_a= ggr.tid_stg EXISTS (select distinct lehr_stg_ab_ti= d from lehr_stg_ab2fb)

Similarly others also like -- lehr_stg_ab.tid not in (select lehr_stg_ab_tid from lehr_stg_ab2= fb) with NOT EXISTS


=
This should surely going to improve performance dep= ending on results from inner query.

Regards
Dhananjay
OpenSCG<= /span>


On Tuesday= , 8 April 2014 3:06 PM, Manoj Gadi <Manoj.Gadi@uni-konstanz.de> wrote= :
Hi All,

I have = been looking for a solution to a problem where my query is executing for a = long time because it is running into a nested loop problem.

I have d= one explain analyze and it shows the query taking a very long time due to n= ested loops.

On the DB side, there are indices in place for all the requi= red columns. By setting nested loop off there is a drastic increase in perf= ormance (from 40,000 ms to 600 ms) but I know this is not a right practice.=

My postgres version is 9.3.2 on linux.

Please find the link = for the query plan below :

http://explain.depesz.com/s/l9o


Also, f= ind below the query that is being executed.

SELECT DISTINCT
 = ; "Sektion/Fachbereich"."parent",
  "Studienfach"."ltxt",
&nbs= p; SUM(CASE
      WHEN "Studiengang"."faktor" IS NOT NUL= L
      AND "Studiengang"."faktor" >=3D 0 THEN "Studie= ngang"."faktor" * "Studierende"."summe"
      ELSE "Studi= erende"."summe"
  END)

FROM (
  SELECT sos_stg_agg= r.tid_stg, sos_stg_aggr.ca12_staat, sos_stg_aggr.geschlecht, sos_stg_aggr.alter, sos_stg_aggr.hzbart, sos_stg_aggr.hmkfzkz, sos_stg_agg= r.hmkfz, sos_stg_aggr.semkfzkz, sos_stg_aggr.semkfz, sos_stg_aggr.hzbkfzkz,= sos_stg_aggr.hzbkfz, sos_stg_aggr.hrst, sos_stg_aggr.studiengang_nr, sos_s= tg_aggr.fach_nr, sos_stg_aggr.fach_sem_zahl, sos_stg_aggr.sem_rueck_beur_ei= n, sos_stg_aggr.kz_rueck_beur_ein, sos_stg_aggr.klinsem, sos_stg_aggr.hssem= , sos_stg_aggr.stuart, sos_stg_aggr.stutyp, sos_stg_aggr.stufrm, sos_stg_ag= gr.stichtag, sos_stg_aggr.summe, sos_stg_aggr.hzbart_int, sos_stg_aggr.matr= ikel_nr, sos_stg_aggr.ch27_grund_beurl, sos_stg_aggr.ch62_grund_exmatr, sos= _stg_aggr.hzbnote, textcat(sos_stg_aggr.studiengang_nr::text, sos_stg_aggr.= fach_nr::text) AS koepfe_faelle
  FROM sos_stg_aggr

  = union all

  SELECT sos_stg_aggr.tid_stg, sos_stg_aggr.ca12_st= aat, sos_stg_aggr.geschlecht, sos_stg_aggr.alter, sos_stg_aggr.hzbart, sos_= stg_aggr.hmkfzkz, sos_stg_aggr.hmkfz, sos_stg_aggr.semkfzkz, sos_stg_aggr.semkfz, sos_stg_aggr.hzbkfzkz, sos_stg_aggr.hzbkfz, sos_stg_a= ggr.hrst, sos_stg_aggr.studiengang_nr, sos_stg_aggr.fach_nr, sos_stg_aggr.f= ach_sem_zahl, sos_stg_aggr.sem_rueck_beur_ein, sos_stg_aggr.kz_rueck_beur_e= in, sos_stg_aggr.klinsem, sos_stg_aggr.hssem, sos_stg_aggr.stuart, sos_stg_= aggr.stutyp, sos_stg_aggr.stufrm, sos_stg_aggr.stichtag, sos_stg_aggr.summe= , sos_stg_aggr.hzbart_int, sos_stg_aggr.matrikel_nr, sos_stg_aggr.ch27_grun= d_beurl, sos_stg_aggr.ch62_grund_exmatr, sos_stg_aggr.hzbnote, '21' AS koep= fe_faelle
  FROM sos_stg_aggr
  where sos_stg_aggr.tid_st= g in (select distinct lehr_stg_ab_tid from lehr_stg_ab2fb)

) AS "Stu= dierende"
INNER JOIN (
  select astat::integer, trim(druck) as = druck from sos_k_status

) AS "R=FCckmeldestatus"
ON (
  = "Studierende"."kz_rueck_beur_ein" =3D "R=FCckmeldestatus"."astat"
)
I= NNER JOIN (
  select tid, trim(name) as name from sos_stichtag

) AS "Stichtag"
ON (
  "Studierende"."stic= htag" =3D "Stichtag"."tid"
)
INNER JOIN (
  select abschluss= , kz_fach, stg, pversion, regel, trim(text) as text, fb, lehr, anteil, tid,= null as faktor from lehr_stg_ab
where lehr_stg_ab.tid not in (select leh= r_stg_ab_tid from lehr_stg_ab2fb)

union
select abschluss, kz_fach= , stg, pversion, regel, trim(text) as text, lehr_stg_ab2fb.fb, lehr, anteil= , tid,faktor from lehr_stg_ab
inner join lehr_stg_ab2fb
on lehr_stg_a= b2fb.lehr_stg_ab_tid =3D lehr_stg_ab.tid

) AS "Studiengang"
ON (<= br>  "Studierende"."tid_stg" =3D "Studiengang"."tid"
)
INNER JO= IN (
  select astat, astfr, astgrp, fb, trim(ltxt) as ltxt, stg fr= om k_stg

) AS "Studienfach"
ON (
  "Studiengang"."stg" = =3D "Studienfach"."stg"
)
AND (
  "Studienfach"."ltxt" IS NO= T NULL
)
INNER JOIN (
  select instnr, ch110_institut, btrim(druck) as druck, btrim(parent) as parent from unikn_k_fb

) AS= "Sektion/Fachbereich"
ON (
  "Studiengang"."fb" =3D "Sektion/F= achbereich"."instnr"
)
INNER JOIN (
  select apnr, trim(druc= k) as druck from cifx where key=3D613

) AS "H=F6rerstatus"
ON (  "Studierende"."hrst" =3D "H=F6rerstatus"."apnr"
)
WHERE
= (
  "Sektion/Fachbereich"."druck" =3D 'FB Biologie'
)
AND (
  (
      "H=F6rerstatus"."druck" =3D 'Haupt= h=F6rer/in'
      AND "Stichtag"."name" =3D 'Amtl. Statis= tik Land'
      AND "R=FCckmeldestatus"."druck" IN ('Beur= laubung', 'Ersteinschreibung', 'Neueinschreibung', 'R=FCckmeldung')
&nbs= p;     AND "Studierende"."sem_rueck_beur_ein" =3D 20132
 = )
)
GROUP BY
  "Sektion/Fachbereich"."parent",
  = "Studienfach"."ltxt"


According to my analysis, the where clause = after the Union All is taking a lot of time for execution.

Any help with an a= lternative way to represent the query or what the cause of issue would be v= ery helpful.


Thanks in advance,
Manoj


--
Sent = via pgsql-performance mailing list (pgsql-perf= ormance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

=
--2063643590-1735647066-1396989609=:60432--