Thread: Optimize complex join to use where condition before join

Optimize complex join to use where condition before join

From
Sebastian Hennebrueder
Date:
Hello,

I am facing a problem in optimizing the query shown below.

Most queries in the application do only find about 20 to 100 matching rows.

The query joins the table taufgaben_mitarbeiter to taufgaben on which a
condition like the following "where clause" is frequently used.

where
am.fmitarbeiter_id = 54

then there is a nested join to taufgaben -> tprojekt -> tkunden_kst ->
tkunden.

What I would like to achieve is that before joining all the tables that
the join of

taufgaben_mitarbeiter
(... from
taufgaben left join taufgaben_mitarbeiter am
on taufgaben.fid = am.faufgaben_id)

is done and that the where condition is evaluated. Than an index scan to join the other data is run.
What is happening at the moment (if I understood the explain analyze) is that the full join is done and at the end the
wherecondition is done. 

The query with seqscan and nestloop enabled takes about 3 seconds.
The query with both disabled takes 0.52 seconds
The query with only nestlop disabled takes 0.6 seconds
and
with only sesscan disabled takes about 3 seconds.

Below you can find the explain analyze from "seqscan and nestloop enabled" and from both disabled. The problem seems to
beright at the beginning when the rows are badly estimated. 
...
Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"  ->  Nested Loop  (cost=1621.51..1729.28 rows=6 width=2541) (actual time=328.000..3125.000 rows=1118 loops=1)"
...
I am using PostgreSQL 8.0 on Windows

Thank you for any idea



--
Kind Regards / Viele Grüße

Sebastian Hennebrueder

-----
http://www.laliluna.de/tutorials.html
Tutorials for Java, Struts, JavaServer Faces, JSP, Hibernate, EJB and more.

enabled seqscan and nested_loop

explain analyze
SELECT taufgaben.fid AS taufgaben_fid, taufgaben.fprojekt_id AS
    taufgaben_fprojekt_id, taufgaben.fnummer AS taufgaben_fnummer,
    taufgaben.fbudget AS taufgaben_fbudget, taufgaben.ftyp AS taufgaben_ftyp,
    taufgaben.fberechnungsart AS taufgaben_fberechnungsart,
    taufgaben.fverrechnung_extern AS taufgaben_fverrechnung_extern,
    taufgaben.fverrechnungsbasis AS taufgaben_fverrechnungsbasis,
    taufgaben.fstatus AS taufgaben_fstatus, taufgaben.fkurzbeschreibung AS
    taufgaben_fkurzbeschreibung, taufgaben.fansprechpartner AS
    taufgaben_fansprechpartner, taufgaben.fanforderer AS taufgaben_fanforderer,
    taufgaben.fstandort_id AS taufgaben_fstandort_id, taufgaben.fwunschtermin
    AS taufgaben_fwunschtermin, taufgaben.fstarttermin AS
    taufgaben_fstarttermin, taufgaben.fgesamtaufwand AS
    taufgaben_fgesamtaufwand, taufgaben.fistaufwand AS taufgaben_fistaufwand,
    taufgaben.fprio AS taufgaben_fprio, taufgaben.ftester AS taufgaben_ftester,
    taufgaben.ffaellig AS taufgaben_ffaellig, taufgaben.flevel AS
    taufgaben_flevel, taufgaben.fkategorie AS taufgaben_fkategorie,
    taufgaben.feintragbearbeitung AS taufgaben_feintragbearbeitung,
    taufgaben.fbearbeitungsstatus AS taufgaben_fbearbeitungsstatus,
    taufgaben.fsolllimit AS taufgaben_fsolllimit, taufgaben.fistlimit AS
    taufgaben_fistlimit, taufgaben.fpauschalbetrag AS
    taufgaben_fpauschalbetrag, taufgaben.frechnungslaeufe_id AS
    taufgaben_frechnungslaeufe_id, taufgaben.fzuberechnen AS
    taufgaben_fzuberechnen, tprojekte.fid AS tprojekte_fid,
    tprojekte.fbezeichnung AS tprojekte_fbezeichnung, tprojekte.fprojektnummer
    AS tprojekte_fprojektnummer, tprojekte.fbudget AS tprojekte_fbudget,
    tprojekte.fverrechnung_extern AS tprojekte_fverrechnung_extern,
    tprojekte.fstatus AS tprojekte_fstatus, tprojekte.fkunden_kst_id AS
    tprojekte_fkunden_kst_id, tprojekte.fverrechnungsbasis AS
    tprojekte_fverrechnungsbasis, tprojekte.fberechnungsart AS
    tprojekte_fberechnungsart, tprojekte.fprojekttyp AS tprojekte_fprojekttyp,
    tprojekte.fkostentraeger_id AS tprojekte_fkostentraeger_id,
    tprojekte.fprojektleiter_id AS tprojekte_fprojektleiter_id,
    tprojekte.fpauschalsatz AS tprojekte_fpauschalsatz,
    tprojekte.frechnungslaeufe_id AS tprojekte_frechnungslaeufe_id,
    tprojekte.fzuberechnen AS tprojekte_fzuberechnen, tprojekte.faufschlagrel
    AS tprojekte_faufschlagrel, tprojekte.faufschlagabs AS
    tprojekte_faufschlagabs, tprojekte.fbearbeitungsstatus AS
    tprojekte_fbearbeitungsstatus, tuser.fusername AS tuser_fusername,
    tuser.fpassword AS tuser_fpassword, tuser.fvorname AS tuser_fvorname,
    tuser.fnachname AS tuser_fnachname, tuser.fismitarbeiter AS
    tuser_fismitarbeiter, tuser.flevel AS tuser_flevel, tuser.fkuerzel AS
    tuser_fkuerzel, taufgaben.floesungsbeschreibung AS
    taufgaben_floesungsbeschreibung, taufgaben.ffehlerbeschreibung AS
    taufgaben_ffehlerbeschreibung, taufgaben.faufgabenstellung AS
    taufgaben_faufgabenstellung, taufgaben.fkritischeaenderungen AS
    taufgaben_fkritischeaenderungen, taufgaben.fbdeaufgabenersteller_id AS
    taufgaben_fbdeaufgabenersteller_id, taufgaben.fzufaktorieren AS
    taufgaben_fzufaktorieren, tprojekte.fzufaktorieren AS
    tprojekte_fzufaktorieren, taufgaben.fisdirty AS taufgaben_fisdirty,
    taufgaben.fnf_kunde_stunden AS taufgaben_fnf_kunde_stunden,
    taufgaben.fzf_kunde_stunden AS taufgaben_fzf_kunde_stunden,
    taufgaben.fbf_kunde_stunden AS taufgaben_fbf_kunde_stunden,
    taufgaben.fnf_kunde_betrag AS taufgaben_fnf_kunde_betrag,
    taufgaben.fzf_kunde_betrag AS taufgaben_fzf_kunde_betrag,
    taufgaben.fbf_kunde_betrag AS taufgaben_fbf_kunde_betrag,
    tprojekte.feurobudget AS tprojekte_feurobudget, tprojekte.fnf_kunde_stunden
    AS tprojekte_fnf_kunde_stunden, tprojekte.fzf_kunde_stunden AS
    tprojekte_fzf_kunde_stunden, tprojekte.fbf_kunde_stunden AS
    tprojekte_fbf_kunde_stunden, tprojekte.fnf_kunde_betrag AS
    tprojekte_fnf_kunde_betrag, tprojekte.fzf_kunde_betrag AS
    tprojekte_fzf_kunde_betrag, tprojekte.fbf_kunde_betrag AS
    tprojekte_fbf_kunde_betrag, tprojekte.fisdirty AS tprojekte_fisdirty,
    tprojekte.fgesamt_brutto_betrag AS tprojekte_fgesamt_brutto_betrag,
    tprojekte.fgesamt_brutto_stunden AS tprojekte_fgesamt_brutto_stunden,
    tprojekte.fgesamt_netto_stunden AS tprojekte_fgesamt_netto_stunden,
    taufgaben.fgesamt_brutto_stunden AS taufgaben_fgesamt_brutto_stunden,
    taufgaben.fgesamt_brutto_betrag AS taufgaben_fgesamt_brutto_betrag,
    taufgaben.fhinweisgesendet AS taufgaben_fhinweisgesendet,
    taufgaben.fwarnunggesendet AS taufgaben_fwarnunggesendet,
    tprojekte.fhinweisgesendet AS tprojekte_fhinweisgesendet,
    tprojekte.fwarnunggesendet AS tprojekte_fwarnunggesendet,
    tuser.femailadresse AS tuser_femailadresse, taufgaben.fnfgesamtaufwand AS
    taufgaben_fnfgesamtaufwand, taufgaben.fnf_netto_stunden AS
    taufgaben_fnf_netto_stunden, taufgaben.fnf_brutto_stunden AS
    taufgaben_fnf_brutto_stunden, taufgaben.fnfhinweisgesendet AS
    taufgaben_fnfhinweisgesendet, taufgaben.fnfwarnunggesendet AS
    taufgaben_fnfwarnunggesendet, tprojekte.fnfgesamtaufwand AS
    tprojekte_fnfgesamtaufwand, tprojekte.fnf_netto_stunden AS
    tprojekte_fnf_netto_stunden, tprojekte.fnf_brutto_stunden AS
    tprojekte_fnf_brutto_stunden, tprojekte.fnfhinweisgesendet AS
    tprojekte_fnfhinweisgesendet, tprojekte.fnfwarnunggesendet AS
    tprojekte_fnfwarnunggesendet, taufgaben.fhatzeiten AS taufgaben_fhatzeiten,
    tprojekte.fhatzeiten AS tprojekte_fhatzeiten,
    taufgaben.fnichtpublicrechnungsfaehig AS
    taufgaben_fnichtpublicrechnungsfaehig,
    taufgaben.fnichtpublicrechnungsfaehigbetrag AS
    taufgaben_fnichtpublicrechnungsfaehigbetrag, taufgaben.fnichtberechenbar AS
    taufgaben_fnichtberechenbar, taufgaben.fnichtberechenbarbetrag AS
    taufgaben_fnichtberechenbarbetrag, tprojekte.fnichtpublicrechnungsfaehig AS
    tprojekte_fnichtpublicrechnungsfaehig,
    tprojekte.fnichtpublicrechnungsfaehigbetrag AS
    tprojekte_fnichtpublicrechnungsfaehigbetrag, tprojekte.fnichtberechenbar AS
    tprojekte_fnichtberechenbar, tprojekte.fnichtberechenbarbetrag AS
    tprojekte_fnichtberechenbarbetrag, taufgaben.finternertester AS
    taufgaben_finternertester, taufgaben.finterngetestet AS
    taufgaben_finterngetestet, tkunden_kst.fbezeichnung AS tkunden_kst_name,
    tkunden.fname AS tkunden_name, tabteilungen.fname AS tabteilungen_fname,
    tkostenstellen.fnummer AS tkostenstellen_fnummer, tkostentraeger.fnummer AS
    tkostentraeger_fnummer, taufgaben.fanzahlbearbeiter AS
    taufgaben_fanzahlbearbeiter, patchdaten.faufgaben_id AS pataid
FROM
taufgaben_mitarbeiter am
left join


 ((((((((taufgaben LEFT JOIN (
    SELECT DISTINCT taufgaben_patches.faufgaben_id
    FROM taufgaben_patches
    ORDER BY taufgaben_patches.faufgaben_id
    ) patchdaten ON ((taufgaben.fid = patchdaten.faufgaben_id))) JOIN tprojekte
        ON ((taufgaben.fprojekt_id = tprojekte.fid))) JOIN tuser ON
        ((tprojekte.fprojektleiter_id = tuser.fid))) JOIN tkunden_kst ON
        ((tprojekte.fkunden_kst_id = tkunden_kst.fid))) JOIN tkunden ON
        ((tkunden_kst.fkunden_id = tkunden.fid))) JOIN tkostentraeger ON
        ((tprojekte.fkostentraeger_id = tkostentraeger.fid))) JOIN
        tkostenstellen ON ((tkostentraeger.fkostenstellen_id =
        tkostenstellen.fid))) JOIN tabteilungen ON
        ((tkostenstellen.fabteilungen_id = tabteilungen.fid)))
on taufgaben.fid = am.faufgaben_id
where
am.fmitarbeiter_id = 54
and
taufgaben.fbearbeitungsstatus <> 2

"Merge Join  (cost=1729.11..1837.08 rows=1 width=2541) (actual time=531.000..3125.000 rows=62 loops=1)"
"  Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"  ->  Nested Loop  (cost=1621.51..1729.28 rows=6 width=2541) (actual time=328.000..3125.000 rows=1118 loops=1)"
"        Join Filter: ("outer".fprojekt_id = "inner".fid)"
"        ->  Merge Left Join  (cost=1490.70..1497.67 rows=1120 width=1047) (actual time=172.000..220.000 rows=1118
loops=1)"
"              Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"              ->  Sort  (cost=1211.46..1214.26 rows=1120 width=1043) (actual time=109.000..109.000 rows=1118 loops=1)"
"                    Sort Key: taufgaben.fid"
"                    ->  Seq Scan on taufgaben  (cost=0.00..853.88 rows=1120 width=1043) (actual time=0.000..109.000
rows=1120loops=1)" 
"                          Filter: (fbearbeitungsstatus <> 2)"
"              ->  Sort  (cost=279.23..279.73 rows=200 width=4) (actual time=63.000..63.000 rows=4773 loops=1)"
"                    Sort Key: patchdaten.faufgaben_id"
"                    ->  Subquery Scan patchdaten  (cost=0.00..271.59 rows=200 width=4) (actual time=0.000..31.000
rows=4773loops=1)" 
"                          ->  Unique  (cost=0.00..269.59 rows=200 width=4) (actual time=0.000..31.000 rows=4773
loops=1)"
"                                ->  Index Scan using idx_aufpa_aufgabeid on taufgaben_patches  (cost=0.00..253.74
rows=6340width=4) (actual time=0.000..0.000 rows=6340 loops=1)" 
"        ->  Materialize  (cost=130.81..130.85 rows=4 width=1494) (actual time=0.140..0.877 rows=876 loops=1118)"
"              ->  Merge Join  (cost=130.53..130.81 rows=4 width=1494) (actual time=156.000..203.000 rows=876 loops=1)"
"                    Merge Cond: ("outer".fkunden_id = "inner".fid)"
"                    ->  Sort  (cost=127.06..127.08 rows=6 width=1455) (actual time=156.000..156.000 rows=876 loops=1)"
"                          Sort Key: tkunden_kst.fkunden_id"
"                          ->  Merge Join  (cost=126.35..126.99 rows=6 width=1455) (actual time=109.000..140.000
rows=876loops=1)" 
"                                Merge Cond: ("outer".fprojektleiter_id = "inner".fid)"
"                                ->  Sort  (cost=118.57..118.59 rows=9 width=580) (actual time=109.000..109.000
rows=876loops=1)" 
"                                      Sort Key: tprojekte.fprojektleiter_id"
"                                      ->  Merge Join  (cost=117.89..118.43 rows=9 width=580) (actual
time=62.000..93.000rows=876 loops=1)" 
"                                            Merge Cond: ("outer".fkunden_kst_id = "inner".fid)"
"                                            ->  Sort  (cost=114.61..114.69 rows=31 width=508) (actual
time=62.000..62.000rows=876 loops=1)" 
"                                                  Sort Key: tprojekte.fkunden_kst_id"
"                                                  ->  Merge Join  (cost=109.11..113.84 rows=31 width=508) (actual
time=31.000..62.000rows=876 loops=1)" 
"                                                        Merge Cond: ("outer".fid = "inner".fkostentraeger_id)"
"                                                        ->  Sort  (cost=13.40..13.42 rows=7 width=162) (actual
time=0.000..0.000rows=158 loops=1)" 
"                                                              Sort Key: tkostentraeger.fid"
"                                                              ->  Merge Join  (cost=12.41..13.31 rows=7 width=162)
(actualtime=0.000..0.000 rows=158 loops=1)" 
"                                                                    Merge Cond: ("outer".fid =
"inner".fkostenstellen_id)"
"                                                                    ->  Sort  (cost=3.06..3.08 rows=7 width=119)
(actualtime=0.000..0.000 rows=19 loops=1)" 
"                                                                          Sort Key: tkostenstellen.fid"
"                                                                          ->  Merge Join  (cost=2.76..2.96 rows=7
width=119)(actual time=0.000..0.000 rows=19 loops=1)" 
"                                                                                Merge Cond: ("outer".fabteilungen_id =
"inner".fid)"
"                                                                                ->  Sort  (cost=1.59..1.64 rows=19
width=55)(actual time=0.000..0.000 rows=19 loops=1)" 
"                                                                                      Sort Key:
tkostenstellen.fabteilungen_id"
"                                                                                      ->  Seq Scan on tkostenstellen
(cost=0.00..1.19rows=19 width=55) (actual time=0.000..0.000 rows=19 loops=1)" 
"                                                                                ->  Sort  (cost=1.17..1.19 rows=7
width=76)(actual time=0.000..0.000 rows=19 loops=1)" 
"                                                                                      Sort Key: tabteilungen.fid"
"                                                                                      ->  Seq Scan on tabteilungen
(cost=0.00..1.07rows=7 width=76) (actual time=0.000..0.000 rows=7 loops=1)" 
"                                                                    ->  Sort  (cost=9.35..9.74 rows=158 width=55)
(actualtime=0.000..0.000 rows=158 loops=1)" 
"                                                                          Sort Key: tkostentraeger.fkostenstellen_id"
"                                                                          ->  Seq Scan on tkostentraeger
(cost=0.00..3.58rows=158 width=55) (actual time=0.000..0.000 rows=158 loops=1)" 
"                                                        ->  Sort  (cost=95.71..97.90 rows=878 width=354) (actual
time=31.000..31.000rows=877 loops=1)" 
"                                                              Sort Key: tprojekte.fkostentraeger_id"
"                                                              ->  Seq Scan on tprojekte  (cost=0.00..52.78 rows=878
width=354)(actual time=0.000..31.000 rows=878 loops=1)" 
"                                            ->  Sort  (cost=3.28..3.42 rows=58 width=80) (actual time=0.000..0.000
rows=892loops=1)" 
"                                                  Sort Key: tkunden_kst.fid"
"                                                  ->  Seq Scan on tkunden_kst  (cost=0.00..1.58 rows=58 width=80)
(actualtime=0.000..0.000 rows=58 loops=1)" 
"                                ->  Sort  (cost=7.78..8.05 rows=109 width=883) (actual time=0.000..0.000 rows=950
loops=1)"
"                                      Sort Key: tuser.fid"
"                                      ->  Seq Scan on tuser  (cost=0.00..4.09 rows=109 width=883) (actual
time=0.000..0.000rows=109 loops=1)" 
"                    ->  Sort  (cost=3.46..3.56 rows=40 width=51) (actual time=0.000..0.000 rows=887 loops=1)"
"                          Sort Key: tkunden.fid"
"                          ->  Seq Scan on tkunden  (cost=0.00..2.40 rows=40 width=51) (actual time=0.000..0.000
rows=40loops=1)" 
"  ->  Sort  (cost=107.60..107.69 rows=35 width=4) (actual time=0.000..0.000 rows=765 loops=1)"
"        Sort Key: am.faufgaben_id"
"        ->  Index Scan using idx_tauf_mit_mitid on taufgaben_mitarbeiter am  (cost=0.00..106.70 rows=35 width=4)
(actualtime=0.000..0.000 rows=765 loops=1)" 
"              Index Cond: (fmitarbeiter_id = 54)"
"Total runtime: 3125.000 ms"


############################################################################
set enable_nestloop to off;
set enable_seqscan to off;


"Merge Join  (cost=4230.83..4231.04 rows=1 width=2541) (actual time=485.000..500.000 rows=62 loops=1)"
"  Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"  ->  Sort  (cost=4123.23..4123.24 rows=6 width=2541) (actual time=469.000..485.000 rows=1118 loops=1)"
"        Sort Key: taufgaben.fid"
"        ->  Merge Join  (cost=4117.47..4123.15 rows=6 width=2541) (actual time=297.000..406.000 rows=1120 loops=1)"
"              Merge Cond: ("outer".fid = "inner".fprojekt_id)"
"              ->  Sort  (cost=263.53..263.54 rows=4 width=1494) (actual time=141.000..141.000 rows=876 loops=1)"
"                    Sort Key: tprojekte.fid"
"                    ->  Merge Join  (cost=247.95..263.49 rows=4 width=1494) (actual time=94.000..109.000 rows=876
loops=1)"
"                          Merge Cond: ("outer".fprojektleiter_id = "inner".fid)"
"                          ->  Sort  (cost=247.95..247.96 rows=7 width=619) (actual time=94.000..94.000 rows=876
loops=1)"
"                                Sort Key: tprojekte.fprojektleiter_id"
"                                ->  Merge Join  (cost=246.86..247.85 rows=7 width=619) (actual time=47.000..78.000
rows=876loops=1)" 
"                                      Merge Cond: ("outer".fkostentraeger_id = "inner".fid)"
"                                      ->  Sort  (cost=222.01..222.45 rows=176 width=465) (actual time=47.000..47.000
rows=878loops=1)" 
"                                            Sort Key: tprojekte.fkostentraeger_id"
"                                            ->  Merge Join  (cost=20.63..215.44 rows=176 width=465) (actual
time=0.000..32.000rows=878 loops=1)" 
"                                                  Merge Cond: ("outer".fid = "inner".fkunden_kst_id)"
"                                                  ->  Sort  (cost=20.63..20.73 rows=40 width=119) (actual
time=0.000..0.000rows=58 loops=1)" 
"                                                        Sort Key: tkunden_kst.fid"
"                                                        ->  Merge Join  (cost=8.34..19.57 rows=40 width=119) (actual
time=0.000..0.000rows=58 loops=1)" 
"                                                              Merge Cond: ("outer".fkunden_id = "inner".fid)"
"                                                              ->  Sort  (cost=8.34..8.48 rows=58 width=80) (actual
time=0.000..0.000rows=58 loops=1)" 
"                                                                    Sort Key: tkunden_kst.fkunden_id"
"                                                                    ->  Index Scan using pk__kunden_kst__30c33ec3 on
tkunden_kst (cost=0.00..6.64 rows=58 width=80) (actual time=0.000..0.000 rows=58 loops=1)" 
"                                                              ->  Index Scan using tkunden_tbl_kunden_pk on tkunden
(cost=0.00..10.44rows=40 width=51) (actual time=0.000..0.000 rows=59 loops=1)" 
"                                                  ->  Index Scan using idx_kunden_kst_id on tprojekte
(cost=0.00..190.66rows=878 width=354) (actual time=0.000..0.000 rows=878 loops=1)" 
"                                      ->  Sort  (cost=24.86..24.87 rows=7 width=162) (actual time=0.000..0.000
rows=923loops=1)" 
"                                            Sort Key: tkostentraeger.fid"
"                                            ->  Merge Join  (cost=12.52..24.76 rows=7 width=162) (actual
time=0.000..0.000rows=158 loops=1)" 
"                                                  Merge Cond: ("outer".fid = "inner".fkostenstellen_id)"
"                                                  ->  Sort  (cost=12.52..12.54 rows=7 width=119) (actual
time=0.000..0.000rows=19 loops=1)" 
"                                                        Sort Key: tkostenstellen.fid"
"                                                        ->  Merge Join  (cost=0.00..12.42 rows=7 width=119) (actual
time=0.000..0.000rows=19 loops=1)" 
"                                                              Merge Cond: ("outer".fabteilungen_id = "inner".fid)"
"                                                              ->  Index Scan using abteilungkostenstellen on
tkostenstellen (cost=0.00..6.21 rows=19 width=55) (actual time=0.000..0.000 rows=19 loops=1)" 
"                                                              ->  Index Scan using fld_id on tabteilungen
(cost=0.00..6.08rows=7 width=76) (actual time=0.000..0.000 rows=19 loops=1)" 
"                                                  ->  Index Scan using idx_kostenstellen_id on tkostentraeger
(cost=0.00..11.74rows=158 width=55) (actual time=0.000..0.000 rows=158 loops=1)" 
"                          ->  Index Scan using pk_tuser on tuser  (cost=0.00..15.20 rows=109 width=883) (actual
time=0.000..0.000rows=950 loops=1)" 
"              ->  Sort  (cost=3853.94..3856.74 rows=1120 width=1047) (actual time=156.000..156.000 rows=1120 loops=1)"
"                    Sort Key: taufgaben.fprojekt_id"
"                    ->  Merge Left Join  (cost=279.23..3496.35 rows=1120 width=1047) (actual time=47.000..156.000
rows=1120loops=1)" 
"                          Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"                          ->  Index Scan using idx_taufgaben_fid on taufgaben  (cost=0.00..3212.95 rows=1120
width=1043)(actual time=0.000..31.000 rows=1120 loops=1)" 
"                                Filter: (fbearbeitungsstatus <> 2)"
"                          ->  Sort  (cost=279.23..279.73 rows=200 width=4) (actual time=47.000..47.000 rows=4773
loops=1)"
"                                Sort Key: patchdaten.faufgaben_id"
"                                ->  Subquery Scan patchdaten  (cost=0.00..271.59 rows=200 width=4) (actual
time=0.000..31.000rows=4773 loops=1)" 
"                                      ->  Unique  (cost=0.00..269.59 rows=200 width=4) (actual time=0.000..31.000
rows=4773loops=1)" 
"                                            ->  Index Scan using idx_aufpa_aufgabeid on taufgaben_patches
(cost=0.00..253.74rows=6340 width=4) (actual time=0.000..16.000 rows=6340 loops=1)" 
"  ->  Sort  (cost=107.60..107.69 rows=35 width=4) (actual time=0.000..0.000 rows=765 loops=1)"
"        Sort Key: am.faufgaben_id"
"        ->  Index Scan using idx_tauf_mit_mitid on taufgaben_mitarbeiter am  (cost=0.00..106.70 rows=35 width=4)
(actualtime=0.000..0.000 rows=765 loops=1)" 
"              Index Cond: (fmitarbeiter_id = 54)"
"Total runtime: 500.000 ms"



Re: Optimize complex join to use where condition before

From
Sebastian Hennebrueder
Date:
Solution to my problem.
I added indexes to each foreign_key (there had been some missing). I
will try tomorrow by daylight what influence this had actually. Only the
indexes did not change anything! Even with lower random_page_costs and
higher shared mem.

The big change was the following
I created a view which holds a part of the query. The part is the nested
join I am doing from rpojekt, tkunden_kst, ....
See below

Than I changed my query to include the view which improved the
performance from 3000 to 450 ms which is quite good now.

But I am having two more question
a) ###############
I estimated the theoretical speed a little bit higher.
The query without joining the view takes about 220 ms. A query to the
view with a condition projekt_id in ( x,y,z), beeing x,y,z all the
projekt I got with the first query, takes 32 ms.
So my calculation is
query a 220
query b to view with project in ... 32
= 252 ms
+ some time to add the adequate row from query b to one of the 62 rows
from query a
This sometime seems to be quite high with 200 ms

or alternative
query a 220 ms
for each of the 62 rows a query to the view with project_id = x
220
62*2 ms
= 344 ms + some time to assemble all this.
=> 100 ms for assembling. This is quite a lot or am I wrong

b) ###################
My query does take about 200 ms. Most of the time is taken by the
following part
LEFT JOIN (
    SELECT DISTINCT taufgaben_patches.faufgaben_id
    FROM taufgaben_patches
    ORDER BY taufgaben_patches.faufgaben_id
    ) patchdaten ON taufgaben.fid = patchdaten.faufgaben_id

What I want to achieve is one column in my query beeing null or not null
and indicating if there is a patch which includes the aufgabe (engl.: task)
Is there a better way?

--
Kind Regards / Viele Grüße

Sebastian Hennebrueder

-----
http://www.laliluna.de/tutorials.html
Tutorials for Java, Struts, JavaServer Faces, JSP, Hibernate, EJB and more.

##################

Below you can find
query solution I found
explain analyze of the complete query (my solution)
explain analyze of query a
explain analyze of view with one project_id as condition


explain analyze
SELECT taufgaben.fid AS taufgaben_fid,
taufgaben.fprojekt_id AS    taufgaben_fprojekt_id,
taufgaben.fnummer AS taufgaben_fnummer,
  taufgaben.fbudget AS taufgaben_fbudget,
  taufgaben.ftyp AS taufgaben_ftyp,
    taufgaben.fberechnungsart AS taufgaben_fberechnungsart,
    taufgaben.fverrechnung_extern AS taufgaben_fverrechnung_extern,
    taufgaben.fverrechnungsbasis AS taufgaben_fverrechnungsbasis,
    taufgaben.fstatus AS taufgaben_fstatus, taufgaben.fkurzbeschreibung AS
    taufgaben_fkurzbeschreibung, taufgaben.fansprechpartner AS
    taufgaben_fansprechpartner, taufgaben.fanforderer AS
taufgaben_fanforderer,
    taufgaben.fstandort_id AS taufgaben_fstandort_id,
taufgaben.fwunschtermin
    AS taufgaben_fwunschtermin, taufgaben.fstarttermin AS
    taufgaben_fstarttermin, taufgaben.fgesamtaufwand AS
    taufgaben_fgesamtaufwand, taufgaben.fistaufwand AS
taufgaben_fistaufwand,
    taufgaben.fprio AS taufgaben_fprio, taufgaben.ftester AS
taufgaben_ftester,
    taufgaben.ffaellig AS taufgaben_ffaellig, taufgaben.flevel AS
    taufgaben_flevel, taufgaben.fkategorie AS taufgaben_fkategorie,
    taufgaben.feintragbearbeitung AS taufgaben_feintragbearbeitung,
    taufgaben.fbearbeitungsstatus AS taufgaben_fbearbeitungsstatus,
    taufgaben.fsolllimit AS taufgaben_fsolllimit, taufgaben.fistlimit AS
    taufgaben_fistlimit, taufgaben.fpauschalbetrag AS
    taufgaben_fpauschalbetrag, taufgaben.frechnungslaeufe_id AS
    taufgaben_frechnungslaeufe_id, taufgaben.fzuberechnen AS
    taufgaben_fzuberechnen,
taufgaben.floesungsbeschreibung AS
    taufgaben_floesungsbeschreibung, taufgaben.ffehlerbeschreibung AS
    taufgaben_ffehlerbeschreibung, taufgaben.faufgabenstellung AS
    taufgaben_faufgabenstellung, taufgaben.fkritischeaenderungen AS
    taufgaben_fkritischeaenderungen, taufgaben.fbdeaufgabenersteller_id AS
    taufgaben_fbdeaufgabenersteller_id, taufgaben.fzufaktorieren AS
    taufgaben_fzufaktorieren,
    taufgaben.fisdirty AS taufgaben_fisdirty,
    taufgaben.fnf_kunde_stunden AS taufgaben_fnf_kunde_stunden,
    taufgaben.fzf_kunde_stunden AS taufgaben_fzf_kunde_stunden,
    taufgaben.fbf_kunde_stunden AS taufgaben_fbf_kunde_stunden,
    taufgaben.fnf_kunde_betrag AS taufgaben_fnf_kunde_betrag,
    taufgaben.fzf_kunde_betrag AS taufgaben_fzf_kunde_betrag,
    taufgaben.fbf_kunde_betrag AS taufgaben_fbf_kunde_betrag,
    taufgaben.fgesamt_brutto_stunden AS taufgaben_fgesamt_brutto_stunden,
    taufgaben.fgesamt_brutto_betrag AS taufgaben_fgesamt_brutto_betrag,
    taufgaben.fhinweisgesendet AS taufgaben_fhinweisgesendet,
    taufgaben.fwarnunggesendet AS taufgaben_fwarnunggesendet,
    taufgaben.fnfgesamtaufwand AS
    taufgaben_fnfgesamtaufwand, taufgaben.fnf_netto_stunden AS
    taufgaben_fnf_netto_stunden, taufgaben.fnf_brutto_stunden AS
    taufgaben_fnf_brutto_stunden, taufgaben.fnfhinweisgesendet AS
    taufgaben_fnfhinweisgesendet, taufgaben.fnfwarnunggesendet AS
    taufgaben_fnfwarnunggesendet,
taufgaben.fhatzeiten AS taufgaben_fhatzeiten,
    taufgaben.fnichtpublicrechnungsfaehig AS
    taufgaben_fnichtpublicrechnungsfaehig,
    taufgaben.fnichtpublicrechnungsfaehigbetrag AS
    taufgaben_fnichtpublicrechnungsfaehigbetrag,
taufgaben.fnichtberechenbar AS
    taufgaben_fnichtberechenbar, taufgaben.fnichtberechenbarbetrag AS
    taufgaben_fnichtberechenbarbetrag,
    taufgaben.finternertester AS
    taufgaben_finternertester, taufgaben.finterngetestet AS
    taufgaben_finterngetestet,
taufgaben.fanzahlbearbeiter AS taufgaben_fanzahlbearbeiter,

    patchdaten.faufgaben_id AS pataid
    , vprojekt.*
FROM
taufgaben
LEFT JOIN (
    SELECT DISTINCT taufgaben_patches.faufgaben_id
    FROM taufgaben_patches
    ORDER BY taufgaben_patches.faufgaben_id
    ) patchdaten ON taufgaben.fid = patchdaten.faufgaben_id
left join taufgaben_mitarbeiter am on taufgaben.fid = am.faufgaben_id
join vprojekt on taufgaben.fprojekt_id = vprojekt.tprojekte_fid
where
am.fmitarbeiter_id = 54
and
taufgaben.fbearbeitungsstatus <> 2

;
and got the following:

"Merge Join  (cost=1739.31..1739.38 rows=1 width=2541) (actual
time=438.000..454.000 rows=62 loops=1)"
"  Merge Cond: ("outer".fprojekt_id = "inner".fid)"
"  ->  Sort  (cost=1608.41..1608.43 rows=7 width=1047) (actual
time=235.000..235.000 rows=62 loops=1)"
"        Sort Key: taufgaben.fprojekt_id"
"        ->  Merge Join  (cost=1598.30..1608.31 rows=7 width=1047)
(actual time=172.000..235.000 rows=62 loops=1)"
"              Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"              ->  Merge Left Join  (cost=1490.70..1497.67 rows=1120
width=1047) (actual time=157.000..235.000 rows=1118 loops=1)"
"                    Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"                    ->  Sort  (cost=1211.46..1214.26 rows=1120
width=1043) (actual time=94.000..94.000 rows=1118 loops=1)"
"                          Sort Key: taufgaben.fid"
"                          ->  Seq Scan on taufgaben  (cost=0.00..853.88
rows=1120 width=1043) (actual time=0.000..94.000 rows=1120 loops=1)"
"                                Filter: (fbearbeitungsstatus <> 2)"
"                    ->  Sort  (cost=279.23..279.73 rows=200 width=4)
(actual time=63.000..63.000 rows=4773 loops=1)"
"                          Sort Key: patchdaten.faufgaben_id"
"                          ->  Subquery Scan patchdaten
(cost=0.00..271.59 rows=200 width=4) (actual time=0.000..16.000
rows=4773 loops=1)"
"                                ->  Unique  (cost=0.00..269.59 rows=200
width=4) (actual time=0.000..16.000 rows=4773 loops=1)"
"                                      ->  Index Scan using
idx_aufpa_aufgabeid on taufgaben_patches  (cost=0.00..253.74 rows=6340
width=4) (actual time=0.000..16.000 rows=6340 loops=1)"
"              ->  Sort  (cost=107.60..107.69 rows=35 width=4) (actual
time=0.000..0.000 rows=765 loops=1)"
"                    Sort Key: am.faufgaben_id"
"                    ->  Index Scan using idx_tauf_mit_mitid on
taufgaben_mitarbeiter am  (cost=0.00..106.70 rows=35 width=4) (actual
time=0.000..0.000 rows=765 loops=1)"
"                          Index Cond: (fmitarbeiter_id = 54)"
"  ->  Sort  (cost=130.90..130.91 rows=6 width=1494) (actual
time=203.000..203.000 rows=916 loops=1)"
"        Sort Key: tprojekte.fid"
"        ->  Merge Join  (cost=130.53..130.82 rows=6 width=1494) (actual
time=156.000..203.000 rows=876 loops=1)"
"              Merge Cond: ("outer".fkunden_id = "inner".fid)"
"              ->  Sort  (cost=127.06..127.08 rows=6 width=1455) (actual
time=156.000..156.000 rows=876 loops=1)"
"                    Sort Key: tkunden_kst.fkunden_id"
"                    ->  Merge Join  (cost=126.35..126.99 rows=6
width=1455) (actual time=125.000..156.000 rows=876 loops=1)"
"                          Merge Cond: ("outer".fprojektleiter_id =
"inner".fid)"
"                          ->  Sort  (cost=118.57..118.59 rows=9
width=580) (actual time=109.000..109.000 rows=876 loops=1)"
"                                Sort Key: tprojekte.fprojektleiter_id"
"                                ->  Merge Join  (cost=117.89..118.43
rows=9 width=580) (actual time=62.000..94.000 rows=876 loops=1)"
"                                      Merge Cond:
("outer".fkunden_kst_id = "inner".fid)"
"                                      ->  Sort  (cost=114.61..114.69
rows=31 width=508) (actual time=62.000..62.000 rows=876 loops=1)"
"                                            Sort Key:
tprojekte.fkunden_kst_id"
"                                            ->  Merge Join
(cost=109.11..113.84 rows=31 width=508) (actual time=31.000..62.000
rows=876 loops=1)"
"                                                  Merge Cond:
("outer".fid = "inner".fkostentraeger_id)"
"                                                  ->  Sort
(cost=13.40..13.42 rows=7 width=162) (actual time=0.000..0.000 rows=158
loops=1)"
"                                                        Sort Key:
tkostentraeger.fid"
"                                                        ->  Merge Join
(cost=12.41..13.31 rows=7 width=162) (actual time=0.000..0.000 rows=158
loops=1)"
"                                                              Merge
Cond: ("outer".fid = "inner".fkostenstellen_id)"
"                                                              ->  Sort
(cost=3.06..3.08 rows=7 width=119) (actual time=0.000..0.000 rows=19
loops=1)"
"
Sort Key: tkostenstellen.fid"
"                                                                    ->
Merge Join  (cost=2.76..2.96 rows=7 width=119) (actual time=0.000..0.000
rows=19 loops=1)"
"
Merge Cond: ("outer".fabteilungen_id = "inner".fid)"
"
->  Sort  (cost=1.59..1.64 rows=19 width=55) (actual time=0.000..0.000
rows=19 loops=1)"
"

Sort Key: tkostenstellen.fabteilungen_id"
"

->  Seq Scan on tkostenstellen  (cost=0.00..1.19 rows=19 width=55)
(actual time=0.000..0.000 rows=19 loops=1)"
"
->  Sort  (cost=1.17..1.19 rows=7 width=76) (actual time=0.000..0.000
rows=19 loops=1)"
"

Sort Key: tabteilungen.fid"
"

->  Seq Scan on tabteilungen  (cost=0.00..1.07 rows=7 width=76) (actual
time=0.000..0.000 rows=7 loops=1)"
"                                                              ->  Sort
(cost=9.35..9.74 rows=158 width=55) (actual time=0.000..0.000 rows=158
loops=1)"
"
Sort Key: tkostentraeger.fkostenstellen_id"
"                                                                    ->
Seq Scan on tkostentraeger  (cost=0.00..3.58 rows=158 width=55) (actual
time=0.000..0.000 rows=158 loops=1)"
"                                                  ->  Sort
(cost=95.71..97.90 rows=878 width=354) (actual time=31.000..46.000
rows=877 loops=1)"
"                                                        Sort Key:
tprojekte.fkostentraeger_id"
"                                                        ->  Seq Scan on
tprojekte  (cost=0.00..52.78 rows=878 width=354) (actual
time=0.000..31.000 rows=878 loops=1)"
"                                      ->  Sort  (cost=3.28..3.42
rows=58 width=80) (actual time=0.000..0.000 rows=892 loops=1)"
"                                            Sort Key: tkunden_kst.fid"
"                                            ->  Seq Scan on
tkunden_kst  (cost=0.00..1.58 rows=58 width=80) (actual
time=0.000..0.000 rows=58 loops=1)"
"                          ->  Sort  (cost=7.78..8.05 rows=109
width=883) (actual time=16.000..16.000 rows=950 loops=1)"
"                                Sort Key: tuser.fid"
"                                ->  Seq Scan on tuser  (cost=0.00..4.09
rows=109 width=883) (actual time=0.000..0.000 rows=109 loops=1)"
"              ->  Sort  (cost=3.46..3.56 rows=40 width=51) (actual
time=0.000..0.000 rows=887 loops=1)"
"                    Sort Key: tkunden.fid"
"                    ->  Seq Scan on tkunden  (cost=0.00..2.40 rows=40
width=51) (actual time=0.000..0.000 rows=40 loops=1)"
"Total runtime: 454.000 ms"




CREATE OR REPLACE VIEW "public"."vprojekt"
AS
SELECT tprojekte.fid AS tprojekte_fid, tprojekte.fbezeichnung AS
    tprojekte_fbezeichnung, tprojekte.fprojektnummer AS
    tprojekte_fprojektnummer, tprojekte.fbudget AS tprojekte_fbudget,
    tprojekte.fverrechnung_extern AS tprojekte_fverrechnung_extern,
    tprojekte.fstatus AS tprojekte_fstatus, tprojekte.fkunden_kst_id AS
    tprojekte_fkunden_kst_id, tprojekte.fverrechnungsbasis AS
    tprojekte_fverrechnungsbasis, tprojekte.fberechnungsart AS
    tprojekte_fberechnungsart, tprojekte.fprojekttyp AS
tprojekte_fprojekttyp,
    tprojekte.fkostentraeger_id AS tprojekte_fkostentraeger_id,
    tprojekte.fprojektleiter_id AS tprojekte_fprojektleiter_id,
    tprojekte.fpauschalsatz AS tprojekte_fpauschalsatz,
    tprojekte.frechnungslaeufe_id AS tprojekte_frechnungslaeufe_id,
    tprojekte.fzuberechnen AS tprojekte_fzuberechnen,
tprojekte.faufschlagrel
    AS tprojekte_faufschlagrel, tprojekte.faufschlagabs AS
    tprojekte_faufschlagabs, tprojekte.fbearbeitungsstatus AS
    tprojekte_fbearbeitungsstatus, tprojekte.fzufaktorieren AS
    tprojekte_fzufaktorieren, tprojekte.feurobudget AS
tprojekte_feurobudget,
    tprojekte.fnf_kunde_stunden AS tprojekte_fnf_kunde_stunden,
    tprojekte.fzf_kunde_stunden AS tprojekte_fzf_kunde_stunden,
    tprojekte.fbf_kunde_stunden AS tprojekte_fbf_kunde_stunden,
    tprojekte.fnf_kunde_betrag AS tprojekte_fnf_kunde_betrag,
    tprojekte.fzf_kunde_betrag AS tprojekte_fzf_kunde_betrag,
    tprojekte.fbf_kunde_betrag AS tprojekte_fbf_kunde_betrag,
    tprojekte.fisdirty AS tprojekte_fisdirty,
tprojekte.fgesamt_brutto_betrag
    AS tprojekte_fgesamt_brutto_betrag, tprojekte.fgesamt_brutto_stunden AS
    tprojekte_fgesamt_brutto_stunden, tprojekte.fgesamt_netto_stunden AS
    tprojekte_fgesamt_netto_stunden, tprojekte.fhinweisgesendet AS
    tprojekte_fhinweisgesendet, tprojekte.fwarnunggesendet AS
    tprojekte_fwarnunggesendet, tprojekte.fnfgesamtaufwand AS
    tprojekte_fnfgesamtaufwand, tprojekte.fnf_netto_stunden AS
    tprojekte_fnf_netto_stunden, tprojekte.fnf_brutto_stunden AS
    tprojekte_fnf_brutto_stunden, tprojekte.fnfhinweisgesendet AS
    tprojekte_fnfhinweisgesendet, tprojekte.fnfwarnunggesendet AS
    tprojekte_fnfwarnunggesendet, tprojekte.fhatzeiten AS
tprojekte_fhatzeiten,
    tprojekte.fnichtpublicrechnungsfaehig AS
    tprojekte_fnichtpublicrechnungsfaehig,
    tprojekte.fnichtpublicrechnungsfaehigbetrag AS
    tprojekte_fnichtpublicrechnungsfaehigbetrag,
tprojekte.fnichtberechenbar AS
    tprojekte_fnichtberechenbar, tprojekte.fnichtberechenbarbetrag AS
    tprojekte_fnichtberechenbarbetrag, tuser.fusername AS tuser_fusername,
    tuser.fpassword AS tuser_fpassword, tuser.fvorname AS tuser_fvorname,
    tuser.fnachname AS tuser_fnachname, tuser.fismitarbeiter AS
    tuser_fismitarbeiter, tuser.flevel AS tuser_flevel, tuser.fkuerzel AS
    tuser_fkuerzel, tuser.femailadresse AS tuser_femailadresse,
    tkunden_kst.fbezeichnung AS tkunden_kst_name, tkunden.fname AS
    tkunden_name, tabteilungen.fname AS tabteilungen_fname,
    tkostenstellen.fnummer AS tkostenstellen_fnummer,
tkostentraeger.fnummer AS
    tkostentraeger_fnummer
FROM ((((((tprojekte JOIN tuser ON ((tprojekte.fprojektleiter_id =
tuser.fid)))
    JOIN tkunden_kst ON ((tprojekte.fkunden_kst_id = tkunden_kst.fid))) JOIN
    tkunden ON ((tkunden_kst.fkunden_id = tkunden.fid))) JOIN
tkostentraeger ON
    ((tprojekte.fkostentraeger_id = tkostentraeger.fid))) JOIN
tkostenstellen
    ON ((tkostentraeger.fkostenstellen_id = tkostenstellen.fid))) JOIN
    tabteilungen ON ((tkostenstellen.fabteilungen_id = tabteilungen.fid)));




query a

"Merge Join  (cost=1598.30..1608.31 rows=7 width=1047) (actual
time=140.000..218.000 rows=62 loops=1)"
"  Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"  ->  Merge Left Join  (cost=1490.70..1497.67 rows=1120 width=1047)
(actual time=140.000..218.000 rows=1118 loops=1)"
"        Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"        ->  Sort  (cost=1211.46..1214.26 rows=1120 width=1043) (actual
time=78.000..78.000 rows=1118 loops=1)"
"              Sort Key: taufgaben.fid"
"              ->  Seq Scan on taufgaben  (cost=0.00..853.88 rows=1120
width=1043) (actual time=0.000..78.000 rows=1120 loops=1)"
"                    Filter: (fbearbeitungsstatus <> 2)"
"        ->  Sort  (cost=279.23..279.73 rows=200 width=4) (actual
time=62.000..62.000 rows=4773 loops=1)"
"              Sort Key: patchdaten.faufgaben_id"
"              ->  Subquery Scan patchdaten  (cost=0.00..271.59 rows=200
width=4) (actual time=0.000..32.000 rows=4773 loops=1)"
"                    ->  Unique  (cost=0.00..269.59 rows=200 width=4)
(actual time=0.000..16.000 rows=4773 loops=1)"
"                          ->  Index Scan using idx_aufpa_aufgabeid on
taufgaben_patches  (cost=0.00..253.74 rows=6340 width=4) (actual
time=0.000..0.000 rows=6340 loops=1)"
"  ->  Sort  (cost=107.60..107.69 rows=35 width=4) (actual
time=0.000..0.000 rows=765 loops=1)"
"        Sort Key: am.faufgaben_id"
"        ->  Index Scan using idx_tauf_mit_mitid on
taufgaben_mitarbeiter am  (cost=0.00..106.70 rows=35 width=4) (actual
time=0.000..0.000 rows=765 loops=1)"
"              Index Cond: (fmitarbeiter_id = 54)"
"Total runtime: 218.000 ms"



explain analyze
SELECT taufgaben.fid AS taufgaben_fid,
taufgaben.fprojekt_id AS    taufgaben_fprojekt_id,
taufgaben.fnummer AS taufgaben_fnummer,
   taufgaben.fbudget AS taufgaben_fbudget,
   taufgaben.ftyp AS taufgaben_ftyp,
     taufgaben.fberechnungsart AS taufgaben_fberechnungsart,
     taufgaben.fverrechnung_extern AS taufgaben_fverrechnung_extern,
     taufgaben.fverrechnungsbasis AS taufgaben_fverrechnungsbasis,
     taufgaben.fstatus AS taufgaben_fstatus, taufgaben.fkurzbeschreibung AS
     taufgaben_fkurzbeschreibung, taufgaben.fansprechpartner AS
     taufgaben_fansprechpartner, taufgaben.fanforderer AS
taufgaben_fanforderer,
     taufgaben.fstandort_id AS taufgaben_fstandort_id,
taufgaben.fwunschtermin
     AS taufgaben_fwunschtermin, taufgaben.fstarttermin AS
     taufgaben_fstarttermin, taufgaben.fgesamtaufwand AS
     taufgaben_fgesamtaufwand, taufgaben.fistaufwand AS
taufgaben_fistaufwand,
     taufgaben.fprio AS taufgaben_fprio, taufgaben.ftester AS
taufgaben_ftester,
     taufgaben.ffaellig AS taufgaben_ffaellig, taufgaben.flevel AS
     taufgaben_flevel, taufgaben.fkategorie AS taufgaben_fkategorie,
     taufgaben.feintragbearbeitung AS taufgaben_feintragbearbeitung,
     taufgaben.fbearbeitungsstatus AS taufgaben_fbearbeitungsstatus,
     taufgaben.fsolllimit AS taufgaben_fsolllimit, taufgaben.fistlimit AS
     taufgaben_fistlimit, taufgaben.fpauschalbetrag AS
     taufgaben_fpauschalbetrag, taufgaben.frechnungslaeufe_id AS
     taufgaben_frechnungslaeufe_id, taufgaben.fzuberechnen AS
     taufgaben_fzuberechnen,
taufgaben.floesungsbeschreibung AS
     taufgaben_floesungsbeschreibung, taufgaben.ffehlerbeschreibung AS
     taufgaben_ffehlerbeschreibung, taufgaben.faufgabenstellung AS
     taufgaben_faufgabenstellung, taufgaben.fkritischeaenderungen AS
     taufgaben_fkritischeaenderungen, taufgaben.fbdeaufgabenersteller_id AS
     taufgaben_fbdeaufgabenersteller_id, taufgaben.fzufaktorieren AS
     taufgaben_fzufaktorieren,
     taufgaben.fisdirty AS taufgaben_fisdirty,
     taufgaben.fnf_kunde_stunden AS taufgaben_fnf_kunde_stunden,
     taufgaben.fzf_kunde_stunden AS taufgaben_fzf_kunde_stunden,
     taufgaben.fbf_kunde_stunden AS taufgaben_fbf_kunde_stunden,
     taufgaben.fnf_kunde_betrag AS taufgaben_fnf_kunde_betrag,
     taufgaben.fzf_kunde_betrag AS taufgaben_fzf_kunde_betrag,
     taufgaben.fbf_kunde_betrag AS taufgaben_fbf_kunde_betrag,
     taufgaben.fgesamt_brutto_stunden AS taufgaben_fgesamt_brutto_stunden,
     taufgaben.fgesamt_brutto_betrag AS taufgaben_fgesamt_brutto_betrag,
     taufgaben.fhinweisgesendet AS taufgaben_fhinweisgesendet,
     taufgaben.fwarnunggesendet AS taufgaben_fwarnunggesendet,
     taufgaben.fnfgesamtaufwand AS
     taufgaben_fnfgesamtaufwand, taufgaben.fnf_netto_stunden AS
     taufgaben_fnf_netto_stunden, taufgaben.fnf_brutto_stunden AS
     taufgaben_fnf_brutto_stunden, taufgaben.fnfhinweisgesendet AS
     taufgaben_fnfhinweisgesendet, taufgaben.fnfwarnunggesendet AS
     taufgaben_fnfwarnunggesendet,
taufgaben.fhatzeiten AS taufgaben_fhatzeiten,
     taufgaben.fnichtpublicrechnungsfaehig AS
     taufgaben_fnichtpublicrechnungsfaehig,
     taufgaben.fnichtpublicrechnungsfaehigbetrag AS
     taufgaben_fnichtpublicrechnungsfaehigbetrag,
taufgaben.fnichtberechenbar AS
     taufgaben_fnichtberechenbar, taufgaben.fnichtberechenbarbetrag AS
     taufgaben_fnichtberechenbarbetrag,
     taufgaben.finternertester AS
     taufgaben_finternertester, taufgaben.finterngetestet AS
     taufgaben_finterngetestet,
taufgaben.fanzahlbearbeiter AS taufgaben_fanzahlbearbeiter
,    patchdaten.faufgaben_id AS pataid
  --   , vprojekt.*
FROM
taufgaben
LEFT JOIN (
     SELECT DISTINCT taufgaben_patches.faufgaben_id
     FROM taufgaben_patches
     ) patchdaten ON taufgaben.fid = patchdaten.faufgaben_id

left join taufgaben_mitarbeiter am on taufgaben.fid = am.faufgaben_id

--join vprojekt on taufgaben.fprojekt_id = vprojekt.tprojekte_fid
where
am.fmitarbeiter_id = 54
and
taufgaben.fbearbeitungsstatus <> 2

;



##########################################################

query b using the select from the view

"Nested Loop  (cost=0.00..24.44 rows=1 width=1494) (actual
time=0.000..0.000 rows=1 loops=1)"
"  Join Filter: ("outer".fprojektleiter_id = "inner".fid)"
"  ->  Nested Loop  (cost=0.00..18.98 rows=1 width=619) (actual
time=0.000..0.000 rows=1 loops=1)"
"        Join Filter: ("outer".fabteilungen_id = "inner".fid)"
"        ->  Nested Loop  (cost=0.00..17.83 rows=1 width=555) (actual
time=0.000..0.000 rows=1 loops=1)"
"              Join Filter: ("outer".fkostenstellen_id = "inner".fid)"
"              ->  Nested Loop  (cost=0.00..16.40 rows=1 width=512)
(actual time=0.000..0.000 rows=1 loops=1)"
"                    ->  Nested Loop  (cost=0.00..11.17 rows=1
width=465) (actual time=0.000..0.000 rows=1 loops=1)"
"                          Join Filter: ("outer".fkunden_id = "inner".fid)"
"                          ->  Nested Loop  (cost=0.00..8.27 rows=1
width=426) (actual time=0.000..0.000 rows=1 loops=1)"
"                                Join Filter: ("outer".fkunden_kst_id =
"inner".fid)"
"                                ->  Index Scan using aaaaaprojekte_pk
on tprojekte  (cost=0.00..5.97 rows=1 width=354) (actual
time=0.000..0.000 rows=1 loops=1)"
"                                      Index Cond: (fid = 2153)"
"                                ->  Seq Scan on tkunden_kst
(cost=0.00..1.58 rows=58 width=80) (actual time=0.000..0.000 rows=58
loops=1)"
"                          ->  Seq Scan on tkunden  (cost=0.00..2.40
rows=40 width=51) (actual time=0.000..0.000 rows=40 loops=1)"
"                    ->  Index Scan using aaaaakostentraeger_pk on
tkostentraeger  (cost=0.00..5.21 rows=1 width=55) (actual
time=0.000..0.000 rows=1 loops=1)"
"                          Index Cond: ("outer".fkostentraeger_id =
tkostentraeger.fid)"
"              ->  Seq Scan on tkostenstellen  (cost=0.00..1.19 rows=19
width=55) (actual time=0.000..0.000 rows=19 loops=1)"
"        ->  Seq Scan on tabteilungen  (cost=0.00..1.07 rows=7 width=76)
(actual time=0.000..0.000 rows=7 loops=1)"
"  ->  Seq Scan on tuser  (cost=0.00..4.09 rows=109 width=883) (actual
time=0.000..0.000 rows=109 loops=1)"
"Total runtime: 0.000 ms"



explain analyze
SELECT tprojekte.fid AS tprojekte_fid, tprojekte.fbezeichnung AS
     tprojekte_fbezeichnung, tprojekte.fprojektnummer AS
     tprojekte_fprojektnummer, tprojekte.fbudget AS tprojekte_fbudget,
     tprojekte.fverrechnung_extern AS tprojekte_fverrechnung_extern,
     tprojekte.fstatus AS tprojekte_fstatus, tprojekte.fkunden_kst_id AS
     tprojekte_fkunden_kst_id, tprojekte.fverrechnungsbasis AS
     tprojekte_fverrechnungsbasis, tprojekte.fberechnungsart AS
     tprojekte_fberechnungsart, tprojekte.fprojekttyp AS
tprojekte_fprojekttyp,
     tprojekte.fkostentraeger_id AS tprojekte_fkostentraeger_id,
     tprojekte.fprojektleiter_id AS tprojekte_fprojektleiter_id,
     tprojekte.fpauschalsatz AS tprojekte_fpauschalsatz,
     tprojekte.frechnungslaeufe_id AS tprojekte_frechnungslaeufe_id,
     tprojekte.fzuberechnen AS tprojekte_fzuberechnen,
tprojekte.faufschlagrel
     AS tprojekte_faufschlagrel, tprojekte.faufschlagabs AS
     tprojekte_faufschlagabs, tprojekte.fbearbeitungsstatus AS
     tprojekte_fbearbeitungsstatus, tprojekte.fzufaktorieren AS
     tprojekte_fzufaktorieren, tprojekte.feurobudget AS
tprojekte_feurobudget,
     tprojekte.fnf_kunde_stunden AS tprojekte_fnf_kunde_stunden,
     tprojekte.fzf_kunde_stunden AS tprojekte_fzf_kunde_stunden,
     tprojekte.fbf_kunde_stunden AS tprojekte_fbf_kunde_stunden,
     tprojekte.fnf_kunde_betrag AS tprojekte_fnf_kunde_betrag,
     tprojekte.fzf_kunde_betrag AS tprojekte_fzf_kunde_betrag,
     tprojekte.fbf_kunde_betrag AS tprojekte_fbf_kunde_betrag,
     tprojekte.fisdirty AS tprojekte_fisdirty,
tprojekte.fgesamt_brutto_betrag
     AS tprojekte_fgesamt_brutto_betrag, tprojekte.fgesamt_brutto_stunden AS
     tprojekte_fgesamt_brutto_stunden, tprojekte.fgesamt_netto_stunden AS
     tprojekte_fgesamt_netto_stunden, tprojekte.fhinweisgesendet AS
     tprojekte_fhinweisgesendet, tprojekte.fwarnunggesendet AS
     tprojekte_fwarnunggesendet, tprojekte.fnfgesamtaufwand AS
     tprojekte_fnfgesamtaufwand, tprojekte.fnf_netto_stunden AS
     tprojekte_fnf_netto_stunden, tprojekte.fnf_brutto_stunden AS
     tprojekte_fnf_brutto_stunden, tprojekte.fnfhinweisgesendet AS
     tprojekte_fnfhinweisgesendet, tprojekte.fnfwarnunggesendet AS
     tprojekte_fnfwarnunggesendet, tprojekte.fhatzeiten AS
tprojekte_fhatzeiten,
     tprojekte.fnichtpublicrechnungsfaehig AS
     tprojekte_fnichtpublicrechnungsfaehig,
     tprojekte.fnichtpublicrechnungsfaehigbetrag AS
     tprojekte_fnichtpublicrechnungsfaehigbetrag,
tprojekte.fnichtberechenbar AS
     tprojekte_fnichtberechenbar, tprojekte.fnichtberechenbarbetrag AS
     tprojekte_fnichtberechenbarbetrag, tuser.fusername AS tuser_fusername,
     tuser.fpassword AS tuser_fpassword, tuser.fvorname AS tuser_fvorname,
     tuser.fnachname AS tuser_fnachname, tuser.fismitarbeiter AS
     tuser_fismitarbeiter, tuser.flevel AS tuser_flevel, tuser.fkuerzel AS
     tuser_fkuerzel, tuser.femailadresse AS tuser_femailadresse,
     tkunden_kst.fbezeichnung AS tkunden_kst_name, tkunden.fname AS
     tkunden_name, tabteilungen.fname AS tabteilungen_fname,
     tkostenstellen.fnummer AS tkostenstellen_fnummer,
tkostentraeger.fnummer AS
     tkostentraeger_fnummer
FROM ((((((tprojekte JOIN tuser ON ((tprojekte.fprojektleiter_id =
tuser.fid)))
     JOIN tkunden_kst ON ((tprojekte.fkunden_kst_id = tkunden_kst.fid)))
JOIN
     tkunden ON ((tkunden_kst.fkunden_id = tkunden.fid))) JOIN
tkostentraeger ON
     ((tprojekte.fkostentraeger_id = tkostentraeger.fid))) JOIN
tkostenstellen
     ON ((tkostentraeger.fkostenstellen_id = tkostenstellen.fid))) JOIN
     tabteilungen ON ((tkostenstellen.fabteilungen_id = tabteilungen.fid)))

where tprojekte.fid = 2153


Re: Optimize complex join to use where condition before

From
Sebastian Hennebrueder
Date:
Solution not found as I thought. I integrated the query in a view and
the query plan became very bad once again.
The reason is that when I am using the view I have the joins in a
differerent order.

Does anyone have an idea to solve this.

Sebastian

a) bad order but the one I have in my application
explain analyze
SELECT taufgaben.fid AS taufgaben_fid, taufgaben.fprojekt_id AS
    taufgaben_fprojekt_id, taufgaben.fnummer AS taufgaben_fnummer,
    taufgaben.fbudget AS taufgaben_fbudget,
    taufgaben.ftyp AS taufgaben_ftyp,
    taufgaben.fberechnungsart AS taufgaben_fberechnungsart,
    taufgaben.fverrechnung_extern AS taufgaben_fverrechnung_extern,
    taufgaben.fverrechnungsbasis AS taufgaben_fverrechnungsbasis,
    taufgaben.fstatus AS taufgaben_fstatus,
    taufgaben.fkurzbeschreibung AS
    taufgaben_fkurzbeschreibung,
    taufgaben.fansprechpartner AS
    taufgaben_fansprechpartner,
    taufgaben.fanforderer AS taufgaben_fanforderer,
    taufgaben.fstandort_id AS taufgaben_fstandort_id,
    taufgaben.fwunschtermin  AS taufgaben_fwunschtermin,
    taufgaben.fstarttermin AS taufgaben_fstarttermin,
    taufgaben.fgesamtaufwand AS taufgaben_fgesamtaufwand,
    taufgaben.fistaufwand AS taufgaben_fistaufwand,
    taufgaben.fprio AS taufgaben_fprio,
    taufgaben.ftester AS taufgaben_ftester,
    taufgaben.ffaellig AS taufgaben_ffaellig,
    taufgaben.flevel AS   taufgaben_flevel,
    taufgaben.fkategorie AS taufgaben_fkategorie,
    taufgaben.feintragbearbeitung AS taufgaben_feintragbearbeitung,
    taufgaben.fbearbeitungsstatus AS taufgaben_fbearbeitungsstatus,
    taufgaben.fsolllimit AS taufgaben_fsolllimit,
    taufgaben.fistlimit AS    taufgaben_fistlimit,
    taufgaben.fpauschalbetrag AS   taufgaben_fpauschalbetrag,
    taufgaben.frechnungslaeufe_id AS   taufgaben_frechnungslaeufe_id,
    taufgaben.fzuberechnen AS   taufgaben_fzuberechnen,
    taufgaben.floesungsbeschreibung AS   taufgaben_floesungsbeschreibung,
    taufgaben.ffehlerbeschreibung AS    taufgaben_ffehlerbeschreibung,
    taufgaben.faufgabenstellung AS   taufgaben_faufgabenstellung,
    taufgaben.fkritischeaenderungen AS    taufgaben_fkritischeaenderungen,
    taufgaben.fbdeaufgabenersteller_id AS
taufgaben_fbdeaufgabenersteller_id,
    taufgaben.fzufaktorieren AS    taufgaben_fzufaktorieren,
    taufgaben.fisdirty AS taufgaben_fisdirty,
    taufgaben.fnf_kunde_stunden AS taufgaben_fnf_kunde_stunden,
    taufgaben.fzf_kunde_stunden AS taufgaben_fzf_kunde_stunden,
    taufgaben.fbf_kunde_stunden AS taufgaben_fbf_kunde_stunden,
    taufgaben.fnf_kunde_betrag AS taufgaben_fnf_kunde_betrag,
    taufgaben.fzf_kunde_betrag AS taufgaben_fzf_kunde_betrag,
    taufgaben.fbf_kunde_betrag AS taufgaben_fbf_kunde_betrag,
    taufgaben.fgesamt_brutto_stunden AS taufgaben_fgesamt_brutto_stunden,
    taufgaben.fgesamt_brutto_betrag AS taufgaben_fgesamt_brutto_betrag,
    taufgaben.fhinweisgesendet AS taufgaben_fhinweisgesendet,
    taufgaben.fwarnunggesendet AS taufgaben_fwarnunggesendet,
    taufgaben.fnfgesamtaufwand AS   taufgaben_fnfgesamtaufwand,
    taufgaben.fnf_netto_stunden AS    taufgaben_fnf_netto_stunden,
    taufgaben.fnf_brutto_stunden AS   taufgaben_fnf_brutto_stunden,
    taufgaben.fnfhinweisgesendet AS   taufgaben_fnfhinweisgesendet,
    taufgaben.fnfwarnunggesendet AS    taufgaben_fnfwarnunggesendet,
    taufgaben.fhatzeiten AS taufgaben_fhatzeiten,
    taufgaben.fnichtpublicrechnungsfaehig AS
taufgaben_fnichtpublicrechnungsfaehig,
    taufgaben.fnichtpublicrechnungsfaehigbetrag AS
taufgaben_fnichtpublicrechnungsfaehigbetrag,
    taufgaben.fnichtberechenbar AS    taufgaben_fnichtberechenbar,
    taufgaben.fnichtberechenbarbetrag AS
taufgaben_fnichtberechenbarbetrag,
    taufgaben.finternertester AS   taufgaben_finternertester,
    taufgaben.finterngetestet AS   taufgaben_finterngetestet,
    taufgaben.fanzahlbearbeiter AS taufgaben_fanzahlbearbeiter,
    patchdaten.faufgaben_id AS pataid
    ,vprojekt.*
FROM taufgaben LEFT JOIN (
    SELECT DISTINCT taufgaben_patches.faufgaben_id
    FROM taufgaben_patches
    ) patchdaten ON taufgaben.fid = patchdaten.faufgaben_id
JOIN vprojekt ON taufgaben.fprojekt_id = vprojekt.tprojekte_fid

join taufgaben_mitarbeiter am on taufgaben.fid = am.faufgaben_id

where
am.fmitarbeiter_id = 54
and
taufgaben.fbearbeitungsstatus <> 2


"Nested Loop  (cost=1349.13..1435.29 rows=1 width=2541) (actual
time=1640.000..3687.000 rows=62 loops=1)"
"  Join Filter: ("inner".fid = "outer".faufgaben_id)"
"  ->  Index Scan using idx_tauf_mit_mitid on taufgaben_mitarbeiter am
(cost=0.00..80.65 rows=35 width=4) (actual time=0.000..0.000 rows=765
loops=1)"
"        Index Cond: (fmitarbeiter_id = 54)"
"  ->  Materialize  (cost=1349.13..1349.20 rows=7 width=2541) (actual
time=0.531..1.570 rows=1120 loops=765)"
"        ->  Merge Join  (cost=1343.42..1349.13 rows=7 width=2541)
(actual time=406.000..515.000 rows=1120 loops=1)"
"              Merge Cond: ("outer".fid = "inner".fprojekt_id)"
"              ->  Sort  (cost=130.89..130.90 rows=6 width=1494) (actual
time=203.000..203.000 rows=876 loops=1)"
"                    Sort Key: tprojekte.fid"
"                    ->  Merge Join  (cost=130.52..130.81 rows=6
width=1494) (actual time=156.000..187.000 rows=876 loops=1)"
"                          Merge Cond: ("outer".fkunden_id = "inner".fid)"
"                          ->  Sort  (cost=127.06..127.07 rows=6
width=1455) (actual time=156.000..156.000 rows=876 loops=1)"
"                                Sort Key: tkunden_kst.fkunden_id"
"                                ->  Merge Join  (cost=126.34..126.98
rows=6 width=1455) (actual time=109.000..125.000 rows=876 loops=1)"
"                                      Merge Cond:
("outer".fprojektleiter_id = "inner".fid)"
"                                      ->  Sort  (cost=118.56..118.58
rows=9 width=580) (actual time=109.000..109.000 rows=876 loops=1)"
"                                            Sort Key:
tprojekte.fprojektleiter_id"
"                                            ->  Merge Join
(cost=117.88..118.42 rows=9 width=580) (actual time=62.000..93.000
rows=876 loops=1)"
"                                                  Merge Cond:
("outer".fkunden_kst_id = "inner".fid)"
"                                                  ->  Sort
(cost=114.60..114.68 rows=31 width=508) (actual time=62.000..62.000
rows=876 loops=1)"
"                                                        Sort Key:
tprojekte.fkunden_kst_id"
"                                                        ->  Merge Join
(cost=109.10..113.84 rows=31 width=508) (actual time=31.000..62.000
rows=876 loops=1)"
"                                                              Merge
Cond: ("outer".fid = "inner".fkostentraeger_id)"
"                                                              ->  Sort
(cost=13.40..13.41 rows=7 width=162) (actual time=0.000..0.000 rows=158
loops=1)"
"
Sort Key: tkostentraeger.fid"
"                                                                    ->
Merge Join  (cost=3.06..13.30 rows=7 width=162) (actual
time=0.000..0.000 rows=158 loops=1)"
"
Merge Cond: ("outer".fkostenstellen_id = "inner".fid)"
"
->  Index Scan using idx_kostenstellen_id on tkostentraeger
(cost=0.00..9.74 rows=158 width=55) (actual time=0.000..0.000 rows=158
loops=1)"
"
->  Sort  (cost=3.06..3.08 rows=7 width=119) (actual time=0.000..0.000
rows=158 loops=1)"
"
Sort Key: tkostenstellen.fid"
"
->  Merge Join  (cost=2.76..2.96 rows=7 width=119) (actual
time=0.000..0.000 rows=19 loops=1)"
"
Merge Cond: ("outer".fabteilungen_id = "inner".fid)"
"
->  Sort  (cost=1.59..1.64 rows=19 width=55) (actual time=0.000..0.000
rows=19 loops=1)"
"
Sort Key: tkostenstellen.fabteilungen_id"
"
->  Seq Scan on tkostenstellen  (cost=0.00..1.19 rows=19 width=55)
(actual time=0.000..0.000 rows=19 loops=1)"
"
->  Sort  (cost=1.17..1.19 rows=7 width=76) (actual time=0.000..0.000
rows=19 loops=1)"
"
Sort Key: tabteilungen.fid"
"
->  Seq Scan on tabteilungen  (cost=0.00..1.07 rows=7 width=76) (actual
time=0.000..0.000 rows=7 loops=1)"
"                                                              ->  Sort
(cost=95.71..97.90 rows=878 width=354) (actual time=31.000..46.000
rows=877 loops=1)"
"
Sort Key: tprojekte.fkostentraeger_id"
"                                                                    ->
Seq Scan on tprojekte  (cost=0.00..52.78 rows=878 width=354) (actual
time=0.000..15.000 rows=878 loops=1)"
"                                                  ->  Sort
(cost=3.28..3.42 rows=58 width=80) (actual time=0.000..0.000 rows=892
loops=1)"
"                                                        Sort Key:
tkunden_kst.fid"
"                                                        ->  Seq Scan on
tkunden_kst  (cost=0.00..1.58 rows=58 width=80) (actual
time=0.000..0.000 rows=58 loops=1)"
"                                      ->  Sort  (cost=7.78..8.05
rows=109 width=883) (actual time=0.000..0.000 rows=950 loops=1)"
"                                            Sort Key: tuser.fid"
"                                            ->  Seq Scan on tuser
(cost=0.00..4.09 rows=109 width=883) (actual time=0.000..0.000 rows=109
loops=1)"
"                          ->  Sort  (cost=3.46..3.56 rows=40 width=51)
(actual time=0.000..0.000 rows=887 loops=1)"
"                                Sort Key: tkunden.fid"
"                                ->  Seq Scan on tkunden
(cost=0.00..2.40 rows=40 width=51) (actual time=0.000..0.000 rows=40
loops=1)"
"              ->  Sort  (cost=1212.53..1215.33 rows=1120 width=1047)
(actual time=203.000..203.000 rows=1120 loops=1)"
"                    Sort Key: taufgaben.fprojekt_id"
"                    ->  Merge Left Join  (cost=1148.83..1155.80
rows=1120 width=1047) (actual time=140.000..203.000 rows=1120 loops=1)"
"                          Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"                          ->  Sort  (cost=910.60..913.40 rows=1120
width=1043) (actual time=78.000..78.000 rows=1120 loops=1)"
"                                Sort Key: taufgaben.fid"
"                                ->  Seq Scan on taufgaben
(cost=0.00..853.88 rows=1120 width=1043) (actual time=0.000..78.000
rows=1120 loops=1)"
"                                      Filter: (fbearbeitungsstatus <> 2)"
"                          ->  Sort  (cost=238.23..238.73 rows=200
width=4) (actual time=62.000..93.000 rows=4773 loops=1)"
"                                Sort Key: patchdaten.faufgaben_id"
"                                ->  Subquery Scan patchdaten
(cost=0.00..230.59 rows=200 width=4) (actual time=0.000..47.000
rows=4773 loops=1)"
"                                      ->  Unique  (cost=0.00..228.59
rows=200 width=4) (actual time=0.000..0.000 rows=4773 loops=1)"
"                                            ->  Index Scan using
idx_aufpa_aufgabeid on taufgaben_patches  (cost=0.00..212.74 rows=6340
width=4) (actual time=0.000..0.000 rows=6340 loops=1)"
"Total runtime: 3703.000 ms"




good order

explain analyze
SELECT taufgaben.fid AS taufgaben_fid, taufgaben.fprojekt_id AS
    taufgaben_fprojekt_id, taufgaben.fnummer AS taufgaben_fnummer,
    taufgaben.fbudget AS taufgaben_fbudget,
    taufgaben.ftyp AS taufgaben_ftyp,
    taufgaben.fberechnungsart AS taufgaben_fberechnungsart,
    taufgaben.fverrechnung_extern AS taufgaben_fverrechnung_extern,
    taufgaben.fverrechnungsbasis AS taufgaben_fverrechnungsbasis,
    taufgaben.fstatus AS taufgaben_fstatus,
    taufgaben.fkurzbeschreibung AS
    taufgaben_fkurzbeschreibung,
    taufgaben.fansprechpartner AS
    taufgaben_fansprechpartner,
    taufgaben.fanforderer AS taufgaben_fanforderer,
    taufgaben.fstandort_id AS taufgaben_fstandort_id,
    taufgaben.fwunschtermin  AS taufgaben_fwunschtermin,
    taufgaben.fstarttermin AS taufgaben_fstarttermin,
    taufgaben.fgesamtaufwand AS taufgaben_fgesamtaufwand,
    taufgaben.fistaufwand AS taufgaben_fistaufwand,
    taufgaben.fprio AS taufgaben_fprio,
    taufgaben.ftester AS taufgaben_ftester,
    taufgaben.ffaellig AS taufgaben_ffaellig,
    taufgaben.flevel AS   taufgaben_flevel,
    taufgaben.fkategorie AS taufgaben_fkategorie,
    taufgaben.feintragbearbeitung AS taufgaben_feintragbearbeitung,
    taufgaben.fbearbeitungsstatus AS taufgaben_fbearbeitungsstatus,
    taufgaben.fsolllimit AS taufgaben_fsolllimit,
    taufgaben.fistlimit AS    taufgaben_fistlimit,
    taufgaben.fpauschalbetrag AS   taufgaben_fpauschalbetrag,
    taufgaben.frechnungslaeufe_id AS   taufgaben_frechnungslaeufe_id,
    taufgaben.fzuberechnen AS   taufgaben_fzuberechnen,
    taufgaben.floesungsbeschreibung AS   taufgaben_floesungsbeschreibung,
    taufgaben.ffehlerbeschreibung AS    taufgaben_ffehlerbeschreibung,
    taufgaben.faufgabenstellung AS   taufgaben_faufgabenstellung,
    taufgaben.fkritischeaenderungen AS    taufgaben_fkritischeaenderungen,
    taufgaben.fbdeaufgabenersteller_id AS
taufgaben_fbdeaufgabenersteller_id,
    taufgaben.fzufaktorieren AS    taufgaben_fzufaktorieren,
    taufgaben.fisdirty AS taufgaben_fisdirty,
    taufgaben.fnf_kunde_stunden AS taufgaben_fnf_kunde_stunden,
    taufgaben.fzf_kunde_stunden AS taufgaben_fzf_kunde_stunden,
    taufgaben.fbf_kunde_stunden AS taufgaben_fbf_kunde_stunden,
    taufgaben.fnf_kunde_betrag AS taufgaben_fnf_kunde_betrag,
    taufgaben.fzf_kunde_betrag AS taufgaben_fzf_kunde_betrag,
    taufgaben.fbf_kunde_betrag AS taufgaben_fbf_kunde_betrag,
    taufgaben.fgesamt_brutto_stunden AS taufgaben_fgesamt_brutto_stunden,
    taufgaben.fgesamt_brutto_betrag AS taufgaben_fgesamt_brutto_betrag,
    taufgaben.fhinweisgesendet AS taufgaben_fhinweisgesendet,
    taufgaben.fwarnunggesendet AS taufgaben_fwarnunggesendet,
    taufgaben.fnfgesamtaufwand AS   taufgaben_fnfgesamtaufwand,
    taufgaben.fnf_netto_stunden AS    taufgaben_fnf_netto_stunden,
    taufgaben.fnf_brutto_stunden AS   taufgaben_fnf_brutto_stunden,
    taufgaben.fnfhinweisgesendet AS   taufgaben_fnfhinweisgesendet,
    taufgaben.fnfwarnunggesendet AS    taufgaben_fnfwarnunggesendet,
    taufgaben.fhatzeiten AS taufgaben_fhatzeiten,
    taufgaben.fnichtpublicrechnungsfaehig AS
taufgaben_fnichtpublicrechnungsfaehig,
    taufgaben.fnichtpublicrechnungsfaehigbetrag AS
taufgaben_fnichtpublicrechnungsfaehigbetrag,
    taufgaben.fnichtberechenbar AS    taufgaben_fnichtberechenbar,
    taufgaben.fnichtberechenbarbetrag AS
taufgaben_fnichtberechenbarbetrag,
    taufgaben.finternertester AS   taufgaben_finternertester,
    taufgaben.finterngetestet AS   taufgaben_finterngetestet,
    taufgaben.fanzahlbearbeiter AS taufgaben_fanzahlbearbeiter,
    patchdaten.faufgaben_id AS pataid
    ,vprojekt.*
FROM taufgaben LEFT JOIN (
    SELECT DISTINCT taufgaben_patches.faufgaben_id
    FROM taufgaben_patches
    ) patchdaten ON taufgaben.fid = patchdaten.faufgaben_id

join taufgaben_mitarbeiter am on taufgaben.fid = am.faufgaben_id

JOIN vprojekt ON taufgaben.fprojekt_id = vprojekt.tprojekte_fid


where
am.fmitarbeiter_id = 54
and
taufgaben.fbearbeitungsstatus <> 2

"Merge Join  (cost=1371.38..1371.45 rows=1 width=2541) (actual
time=422.000..438.000 rows=62 loops=1)"
"  Merge Cond: ("outer".fprojekt_id = "inner".fid)"
"  ->  Sort  (cost=1240.49..1240.51 rows=7 width=1047) (actual
time=219.000..219.000 rows=62 loops=1)"
"        Sort Key: taufgaben.fprojekt_id"
"        ->  Merge Join  (cost=1230.38..1240.39 rows=7 width=1047)
(actual time=157.000..219.000 rows=62 loops=1)"
"              Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"              ->  Merge Left Join  (cost=1148.83..1155.80 rows=1120
width=1047) (actual time=141.000..203.000 rows=1118 loops=1)"
"                    Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"                    ->  Sort  (cost=910.60..913.40 rows=1120
width=1043) (actual time=94.000..94.000 rows=1118 loops=1)"
"                          Sort Key: taufgaben.fid"
"                          ->  Seq Scan on taufgaben  (cost=0.00..853.88
rows=1120 width=1043) (actual time=0.000..94.000 rows=1120 loops=1)"
"                                Filter: (fbearbeitungsstatus <> 2)"
"                    ->  Sort  (cost=238.23..238.73 rows=200 width=4)
(actual time=47.000..47.000 rows=4773 loops=1)"
"                          Sort Key: patchdaten.faufgaben_id"
"                          ->  Subquery Scan patchdaten
(cost=0.00..230.59 rows=200 width=4) (actual time=0.000..47.000
rows=4773 loops=1)"
"                                ->  Unique  (cost=0.00..228.59 rows=200
width=4) (actual time=0.000..31.000 rows=4773 loops=1)"
"                                      ->  Index Scan using
idx_aufpa_aufgabeid on taufgaben_patches  (cost=0.00..212.74 rows=6340
width=4) (actual time=0.000..15.000 rows=6340 loops=1)"
"              ->  Sort  (cost=81.54..81.63 rows=35 width=4) (actual
time=16.000..16.000 rows=765 loops=1)"
"                    Sort Key: am.faufgaben_id"
"                    ->  Index Scan using idx_tauf_mit_mitid on
taufgaben_mitarbeiter am  (cost=0.00..80.65 rows=35 width=4) (actual
time=0.000..16.000 rows=765 loops=1)"
"                          Index Cond: (fmitarbeiter_id = 54)"
"  ->  Sort  (cost=130.89..130.90 rows=6 width=1494) (actual
time=203.000..203.000 rows=916 loops=1)"
"        Sort Key: tprojekte.fid"
"        ->  Merge Join  (cost=130.52..130.81 rows=6 width=1494) (actual
time=156.000..203.000 rows=876 loops=1)"
"              Merge Cond: ("outer".fkunden_id = "inner".fid)"
"              ->  Sort  (cost=127.06..127.07 rows=6 width=1455) (actual
time=156.000..156.000 rows=876 loops=1)"
"                    Sort Key: tkunden_kst.fkunden_id"
"                    ->  Merge Join  (cost=126.34..126.98 rows=6
width=1455) (actual time=110.000..141.000 rows=876 loops=1)"
"                          Merge Cond: ("outer".fprojektleiter_id =
"inner".fid)"
"                          ->  Sort  (cost=118.56..118.58 rows=9
width=580) (actual time=110.000..110.000 rows=876 loops=1)"
"                                Sort Key: tprojekte.fprojektleiter_id"
"                                ->  Merge Join  (cost=117.88..118.42
rows=9 width=580) (actual time=63.000..94.000 rows=876 loops=1)"
"                                      Merge Cond:
("outer".fkunden_kst_id = "inner".fid)"
"                                      ->  Sort  (cost=114.60..114.68
rows=31 width=508) (actual time=63.000..63.000 rows=876 loops=1)"
"                                            Sort Key:
tprojekte.fkunden_kst_id"
"                                            ->  Merge Join
(cost=109.10..113.84 rows=31 width=508) (actual time=31.000..63.000
rows=876 loops=1)"
"                                                  Merge Cond:
("outer".fid = "inner".fkostentraeger_id)"
"                                                  ->  Sort
(cost=13.40..13.41 rows=7 width=162) (actual time=0.000..0.000 rows=158
loops=1)"
"                                                        Sort Key:
tkostentraeger.fid"
"                                                        ->  Merge Join
(cost=3.06..13.30 rows=7 width=162) (actual time=0.000..0.000 rows=158
loops=1)"
"                                                              Merge
Cond: ("outer".fkostenstellen_id = "inner".fid)"
"                                                              ->  Index
Scan using idx_kostenstellen_id on tkostentraeger  (cost=0.00..9.74
rows=158 width=55) (actual time=0.000..0.000 rows=158 loops=1)"
"                                                              ->  Sort
(cost=3.06..3.08 rows=7 width=119) (actual time=0.000..0.000 rows=158
loops=1)"
"
Sort Key: tkostenstellen.fid"
"                                                                    ->
Merge Join  (cost=2.76..2.96 rows=7 width=119) (actual time=0.000..0.000
rows=19 loops=1)"
"
Merge Cond: ("outer".fabteilungen_id = "inner".fid)"
"
->  Sort  (cost=1.59..1.64 rows=19 width=55) (actual time=0.000..0.000
rows=19 loops=1)"
"
Sort Key: tkostenstellen.fabteilungen_id"
"
->  Seq Scan on tkostenstellen  (cost=0.00..1.19 rows=19 width=55)
(actual time=0.000..0.000 rows=19 loops=1)"
"
->  Sort  (cost=1.17..1.19 rows=7 width=76) (actual time=0.000..0.000
rows=19 loops=1)"
"
Sort Key: tabteilungen.fid"
"
->  Seq Scan on tabteilungen  (cost=0.00..1.07 rows=7 width=76) (actual
time=0.000..0.000 rows=7 loops=1)"
"                                                  ->  Sort
(cost=95.71..97.90 rows=878 width=354) (actual time=31.000..31.000
rows=877 loops=1)"
"                                                        Sort Key:
tprojekte.fkostentraeger_id"
"                                                        ->  Seq Scan on
tprojekte  (cost=0.00..52.78 rows=878 width=354) (actual
time=0.000..31.000 rows=878 loops=1)"
"                                      ->  Sort  (cost=3.28..3.42
rows=58 width=80) (actual time=0.000..0.000 rows=892 loops=1)"
"                                            Sort Key: tkunden_kst.fid"
"                                            ->  Seq Scan on
tkunden_kst  (cost=0.00..1.58 rows=58 width=80) (actual
time=0.000..0.000 rows=58 loops=1)"
"                          ->  Sort  (cost=7.78..8.05 rows=109
width=883) (actual time=0.000..0.000 rows=950 loops=1)"
"                                Sort Key: tuser.fid"
"                                ->  Seq Scan on tuser  (cost=0.00..4.09
rows=109 width=883) (actual time=0.000..0.000 rows=109 loops=1)"
"              ->  Sort  (cost=3.46..3.56 rows=40 width=51) (actual
time=0.000..0.000 rows=887 loops=1)"
"                    Sort Key: tkunden.fid"
"                    ->  Seq Scan on tkunden  (cost=0.00..2.40 rows=40
width=51) (actual time=0.000..0.000 rows=40 loops=1)"
"Total runtime: 438.000 ms"



Re: Optimize complex join to use where condition before

From
Greg Stark
Date:
Sebastian Hennebrueder <usenet@laliluna.de> writes:

> User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
> ...
>
> "Nested Loop  (cost=1349.13..1435.29 rows=1 width=2541) (actual
> time=1640.000..3687.000 rows=62 loops=1)"
> "  Join Filter: ("inner".fid = "outer".faufgaben_id)"
> "  ->  Index Scan using idx_tauf_mit_mitid on taufgaben_mitarbeiter am
> (cost=0.00..80.65 rows=35 width=4) (actual time=0.000..0.000 rows=765
> loops=1)"

Is it really Mozilla Thunderbird that's causing this new craptastic mangling
of plans in people's mails? I was assuming it was some new idea of how to mess
up people's mail coming out of Exchange or Lotus or some other such "corporate
messaging" software that only handled SMTP mail as an afterthought. This is,
uh, disappointing.

--
greg

Re: Optimize complex join to use where condition before

From
John A Meinel
Date:
Greg Stark wrote:
> Sebastian Hennebrueder <usenet@laliluna.de> writes:
>
>
>>User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
>>...
>>
>>"Nested Loop  (cost=1349.13..1435.29 rows=1 width=2541) (actual
>>time=1640.000..3687.000 rows=62 loops=1)"
>>"  Join Filter: ("inner".fid = "outer".faufgaben_id)"
>>"  ->  Index Scan using idx_tauf_mit_mitid on taufgaben_mitarbeiter am
>>(cost=0.00..80.65 rows=35 width=4) (actual time=0.000..0.000 rows=765
>>loops=1)"
>
>
> Is it really Mozilla Thunderbird that's causing this new craptastic mangling
> of plans in people's mails? I was assuming it was some new idea of how to mess
> up people's mail coming out of Exchange or Lotus or some other such "corporate
> messaging" software that only handled SMTP mail as an afterthought. This is,
> uh, disappointing.
>

Are you talking about the quotes, or just the fact that it is wrapped?

I don't know where the quotes came from, but in Thunderbird if you are
writing in text mode (not html) it defaults to wrapping the text at
something like 78 characters. That includes copy/paste text.

If you want it to *not* wrap, it turns out that "Paste as quotation"
will not wrap, but then you have to remove the "> " from the beginning
of every line.

In html mode, it also defaults to wrapping, but if you switch to
PREFORMAT text first, it doesn't wrap.

At least, those are the tricks that I've found. Safest bet is to just
use an attachment, though.

John
=:->


Attachment

Re: Optimize complex join to use where condition before

From
Sebastian Hennebrueder
Date:
I found a solution to improve my query. I do not know why but the
statistics for all column has been 0.
I changed this to 10 for index columns and to 20 for all foreign key
columns.
and to 100 for foreign key columns.
I set the random page cost to 2
and now the query runs as expected.

Many thanks to all of the posts in my and in other threads which helped
a lot.

Sebastian

"Merge Join (cost=1325.06..1329.96 rows=6 width=2558) (actual
time=344.000..344.000 rows=6 loops=1)"
" Merge Cond: ("outer".fid = "inner".faufgaben_id)"
" -> Sort (cost=1269.57..1271.91 rows=934 width=2541) (actual
time=344.000..344.000 rows=773 loops=1)"
"  Sort Key: taufgaben.fid"
"  -> Merge Join (cost=1205.09..1223.49 rows=934 width=2541) (actual
time=219.000..313.000 rows=936 loops=1)"
"    Merge Cond: ("outer".fid = "inner".fprojekt_id)"
"    -> Sort (cost=302.08..304.27 rows=876 width=1494) (actual
time=156.000..156.000 rows=876 loops=1)"
"     Sort Key: tprojekte.fid"
"     -> Merge Join (cost=237.42..259.27 rows=876 width=1494) (actual
time=109.000..141.000 rows=876 loops=1)"
"       Merge Cond: ("outer".fid = "inner".fprojektleiter_id)"
"       -> Index Scan using pk_tuser on tuser (cost=0.00..9.13 rows=109
width=883) (actual time=0.000..0.000 rows=101 loops=1)"
"       -> Sort (cost=237.42..239.61 rows=876 width=619) (actual
time=109.000..109.000 rows=876 loops=1)"
"        Sort Key: tprojekte.fprojektleiter_id"
"        -> Merge Join (cost=181.17..194.60 rows=876 width=619) (actual
time=63.000..94.000 rows=876 loops=1)"
"          Merge Cond: ("outer".fid = "inner".fkunden_kst_id)"
"          -> Sort (cost=9.51..9.66 rows=58 width=119) (actual
time=0.000..0.000 rows=58 loops=1)"
"           Sort Key: tkunden_kst.fid"
"           -> Merge Join (cost=6.74..7.81 rows=58 width=119) (actual
time=0.000..0.000 rows=58 loops=1)"
"             Merge Cond: ("outer".fid = "inner".fkunden_id)"
"             -> Sort (cost=3.46..3.56 rows=40 width=51) (actual
time=0.000..0.000 rows=40 loops=1)"
"              Sort Key: tkunden.fid"
"              -> Seq Scan on tkunden (cost=0.00..2.40 rows=40 width=51)
(actual time=0.000..0.000 rows=40 loops=1)"
"             -> Sort (cost=3.28..3.42 rows=58 width=80) (actual
time=0.000..0.000 rows=58 loops=1)"
"              Sort Key: tkunden_kst.fkunden_id"
"              -> Seq Scan on tkunden_kst (cost=0.00..1.58 rows=58
width=80) (actual time=0.000..0.000 rows=58 loops=1)"
"          -> Sort (cost=171.66..173.85 rows=876 width=508) (actual
time=63.000..63.000 rows=876 loops=1)"
"           Sort Key: tprojekte.fkunden_kst_id"
"           -> Merge Join (cost=114.91..128.85 rows=876 width=508)
(actual time=31.000..47.000 rows=876 loops=1)"
"             Merge Cond: ("outer".fid = "inner".fkostentraeger_id)"
"             -> Sort (cost=19.20..19.60 rows=158 width=162) (actual
time=0.000..0.000 rows=158 loops=1)"
"              Sort Key: tkostentraeger.fid"
"              -> Merge Join (cost=3.49..13.43 rows=158 width=162)
(actual time=0.000..0.000 rows=158 loops=1)"
"                Merge Cond: ("outer".fkostenstellen_id = "inner".fid)"
"                -> Index Scan using idx_kostenstellen_id on
tkostentraeger (cost=0.00..7.18 rows=158 width=55) (actual
time=0.000..0.000 rows=158 loops=1)"
"                -> Sort (cost=3.49..3.53 rows=19 width=119) (actual
time=0.000..0.000 rows=158 loops=1)"
"                 Sort Key: tkostenstellen.fid"
"                 -> Merge Join (cost=2.76..3.08 rows=19 width=119)
(actual time=0.000..0.000 rows=19 loops=1)"
"                   Merge Cond: ("outer".fid = "inner".fabteilungen_id)"
"                   -> Sort (cost=1.17..1.19 rows=7 width=76) (actual
time=0.000..0.000 rows=7 loops=1)"
"                    Sort Key: tabteilungen.fid"
"                    -> Seq Scan on tabteilungen (cost=0.00..1.07 rows=7
width=76) (actual time=0.000..0.000 rows=7 loops=1)"
"                   -> Sort (cost=1.59..1.64 rows=19 width=55) (actual
time=0.000..0.000 rows=19 loops=1)"
"                    Sort Key: tkostenstellen.fabteilungen_id"
"                    -> Seq Scan on tkostenstellen (cost=0.00..1.19
rows=19 width=55) (actual time=0.000..0.000 rows=19 loops=1)"
"             -> Sort (cost=95.71..97.90 rows=878 width=354) (actual
time=31.000..31.000 rows=877 loops=1)"
"              Sort Key: tprojekte.fkostentraeger_id"
"              -> Seq Scan on tprojekte (cost=0.00..52.78 rows=878
width=354) (actual time=0.000..31.000 rows=878 loops=1)"
"    -> Sort (cost=903.01..905.35 rows=936 width=1047) (actual
time=63.000..63.000 rows=936 loops=1)"
"     Sort Key: taufgaben.fprojekt_id"
"     -> Nested Loop Left Join (cost=0.28..856.82 rows=936 width=1047)
(actual time=0.000..63.000 rows=936 loops=1)"
"       Join Filter: ("outer".fid = "inner".faufgaben_id)"
"       -> Index Scan using idx_taufgaben_bstatus on taufgaben
(cost=0.00..835.47 rows=936 width=1043) (actual time=0.000..0.000
rows=936 loops=1)"
"        Index Cond: (fbearbeitungsstatus < 2)"
"       -> Materialize (cost=0.28..0.29 rows=1 width=4) (actual
time=0.000..0.000 rows=1 loops=936)"
"        -> Subquery Scan patchdaten (cost=0.00..0.28 rows=1 width=4)
(actual time=0.000..0.000 rows=1 loops=1)"
"          -> Limit (cost=0.00..0.27 rows=1 width=4) (actual
time=0.000..0.000 rows=1 loops=1)"
"           -> Merge Join (cost=0.00..1706.77 rows=6340 width=4) (actual
time=0.000..0.000 rows=1 loops=1)"
"             Merge Cond: ("outer".fid = "inner".faufgaben_id)"
"             -> Index Scan using idx_taufgaben_fid on taufgaben
(cost=0.00..1440.61 rows=6070 width=8) (actual time=0.000..0.000 rows=1
loops=1)"
"             -> Index Scan using idx_aufpa_aufgabeid on
taufgaben_patches (cost=0.00..171.74 rows=6340 width=4) (actual
time=0.000..0.000 rows=1 loops=1)"
" -> Sort (cost=55.49..55.57 rows=35 width=17) (actual time=0.000..0.000
rows=270 loops=1)"
"  Sort Key: am.faufgaben_id"
"  -> Index Scan using idx_tauf_mit_mitid on taufgaben_mitarbeiter am
(cost=0.00..54.59 rows=35 width=17) (actual time=0.000..0.000 rows=270
loops=1)"
"    Index Cond: (fmitarbeiter_id = 58)"
"Total runtime: 344.000 ms"

Re: Optimize complex join to use where condition before

From
John Arbash Meinel
Date:
Sebastian Hennebrueder wrote:

> I found a solution to improve my query. I do not know why but the
> statistics for all column has been 0.
> I changed this to 10 for index columns and to 20 for all foreign key
> columns.
> and to 100 for foreign key columns.
> I set the random page cost to 2
> and now the query runs as expected.
>
> Many thanks to all of the posts in my and in other threads which
> helped a lot.
>
> Sebastian


I think 0 = use default. But still, changing to 20 and 100 probably
fixes your problems.

John
=:->


Attachment