[PERFORM] 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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 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.g...@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 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
