From: Dhananjay Singh <dk_singh99@yahoo.com>
To: Manoj Gadi <Manoj.Gadi@uni-konstanz.de>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Nested loop issue
Date: Wed, 9 Apr 2014 04:40:09 +0800 (SGT)
Message-ID: <1396989609.60432.YahooMailNeo@web193601.mail.sg3.yahoo.com> (raw)
In-Reply-To: <9d3d69016b886789.5343c2eb@limbe.rz.uni-konstanz.de>
References: <9d3d69016b886789.5343c2eb@limbe.rz.uni-konstanz.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.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 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dk_singh99@yahoo.com, Manoj.Gadi@uni-konstanz.de
Subject: Re: Nested loop issue
In-Reply-To: <1396989609.60432.YahooMailNeo@web193601.mail.sg3.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox