Re: Optimize complex join to use where condition before - Mailing list pgsql-performance

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"