Re: Nested loop issue

REPLACE -- where sos_stg_aggr.tid_stg in (select distinct lehr_stg_ab_tid from lehr_stg_ab2fb)

WITH -- where sos_stg_aggr.tid_stg EXISTS (select distinct lehr_stg_ab_tid from lehr_stg_ab2fb)

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

This should surely going to improve performance depending on results from inner query.

Regards
Dhananjay
OpenSCG

On Tuesday, 8 April 2014 3:06 PM, Manoj Gadi <Manoj(dot)Gadi(at)uni-konstanz(dot)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 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 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, 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" >= 0 THEN "Studiengang"."faktor" * "Studierende"."summe"
      ELSE "Studierende"."summe"
  END)

FROM (
  SELECT sos_stg_aggr.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_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_aggr.matrikel_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_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_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_aggr.matrikel_nr, sos_stg_aggr.ch27_grund_beurl, sos_stg_aggr.ch62_grund_exmatr, 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ückmeldestatus"
ON (
  "Studierende"."kz_rueck_beur_ein" = "Rückmeldestatus"."astat"
)
INNER JOIN (
  select tid, trim(name) as name from sos_stichtag

) AS "Stichtag"
ON (
  "Studierende"."stichtag" = "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_stg_ab2fb.fb, lehr, anteil, tid,faktor from lehr_stg_ab
inner join lehr_stg_ab2fb
on lehr_stg_ab2fb.lehr_stg_ab_tid = lehr_stg_ab.tid

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

) AS "Studienfach"
ON (
  "Studiengang"."stg" = "Studienfach"."stg"
)
AND (
  "Studienfach"."ltxt" IS NOT 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" = "Sektion/Fachbereich"."instnr"
)
INNER JOIN (
  select apnr, trim(druck) as druck from cifx where key=613

) AS "Hörerstatus"
ON (
  "Studierende"."hrst" = "Hörerstatus"."apnr"
)
WHERE
(
  "Sektion/Fachbereich"."druck" = 'FB Biologie'
)
AND
(
  (
      "Hörerstatus"."druck" = 'Haupthörer/in'
      AND "Stichtag"."name" = 'Amtl. Statistik Land'
      AND "Rückmeldestatus"."druck" IN ('Beurlaubung', 'Ersteinschreibung', 'Neueinschreibung', 'Rückmeldung')
      AND "Studierende"."sem_rueck_beur_ein" = 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 of issue would be very helpful.

Thanks in advance,
Manoj