User-Agent: SOGoMail 2.2.0
MIME-Version: 1.0
From: "Manoj Gadi" <Manoj.Gadi@uni-konstanz.de>
Subject: Nested loop issue
Message-Id: <9d3d69016b886789.5343c2eb@limbe.rz.uni-konstanz.de>
To: pgsql-performance@postgresql.org
Content-Type: text/plain; charset="utf-8"
Date: Tue, 08 Apr 2014 11:35:39 +0200
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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 done explain analyze and it shows the query taking a very long time =
due to nested loops.

On the DB side, there are indices in place for all the required columns. By=
 setting nested loop off there is a drastic increase in performance (from 4=
0,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, find below the query that is being executed.

SELECT DISTINCT
   "Sektion/Fachbereich"."parent",
   "Studienfach"."ltxt",
   SUM(CASE
      WHEN "Studiengang"."faktor" IS NOT NULL
      AND "Studiengang"."faktor" >=3D 0 THEN "Studiengang"."faktor" * "Stud=
ierende"."summe"
      ELSE "Studierende"."summe"
   END)

FROM (
   SELECT sos_stg_aggr.tid_stg, sos_stg_aggr.ca12_staat, sos_stg_aggr.gesch=
lecht, sos_stg_aggr.alter, sos_stg_aggr.hzbart, sos_stg_aggr.hmkfzkz, sos_s=
tg_aggr.hmkfz, sos_stg_aggr.semkfzkz, sos_stg_aggr.semkfz, sos_stg_aggr.hzb=
kfzkz, 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_b=
eur_ein, 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_aggr.stichtag, sos_stg_aggr.summe, sos_stg_aggr.hzbart_int, sos_stg_agg=
r.matrikel_nr, sos_stg_aggr.ch27_grund_beurl, sos_stg_aggr.ch62_grund_exmat=
r, 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_staat, sos_stg_aggr.gesch=
lecht, sos_stg_aggr.alter, sos_stg_aggr.hzbart, sos_stg_aggr.hmkfzkz, sos_s=
tg_aggr.hmkfz, sos_stg_aggr.semkfzkz, sos_stg_aggr.semkfz, sos_stg_aggr.hzb=
kfzkz, 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_b=
eur_ein, 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_aggr.stichtag, sos_stg_aggr.summe, sos_stg_aggr.hzbart_int, sos_stg_agg=
r.matrikel_nr, sos_stg_aggr.ch27_grund_beurl, sos_stg_aggr.ch62_grund_exmat=
r, sos_stg_aggr.hzbnote, '21' AS koepfe_faelle
   FROM sos_stg_aggr
   where sos_stg_aggr.tid_stg in (select distinct lehr_stg_ab_tid from lehr=
_stg_ab2fb)

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

) AS "R=C3=BCckmeldestatus"
ON (
   "Studierende"."kz_rueck_beur_ein" =3D "R=C3=BCckmeldestatus"."astat"
)
INNER JOIN (
   select tid, trim(name) as name from sos_stichtag

) AS "Stichtag"
ON (
   "Studierende"."stichtag" =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 lehr_stg_ab_tid from lehr_stg_ab2fb)

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

) AS "Studiengang"
ON (
   "Studierende"."tid_stg" =3D "Studiengang"."tid"
)
INNER JOIN (
   select astat, astfr, astgrp, fb, trim(ltxt) as ltxt, stg from k_stg

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

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

) AS "H=C3=B6rerstatus"
ON (
   "Studierende"."hrst" =3D "H=C3=B6rerstatus"."apnr"
)
WHERE
(
   "Sektion/Fachbereich"."druck" =3D 'FB Biologie'
)
AND
 (
   (
      "H=C3=B6rerstatus"."druck" =3D 'Haupth=C3=B6rer/in'
      AND "Stichtag"."name" =3D 'Amtl. Statistik Land'
      AND "R=C3=BCckmeldestatus"."druck" IN ('Beurlaubung', 'Ersteinschreib=
ung', 'Neueinschreibung', 'R=C3=BCckmeldung')
      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 alternative way to represent the query or what the cause o=
f issue would be very helpful.


Thanks in advance,
Manoj


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