Nested loop issue

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