Re: Optimize complex join to use where condition before

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"