Re: performance drop on 8.2.4, reverting to 8.1.4

> Is this with the join collapse limit set to 1, or with
> default? (Default is generally more interesting.)

below is the same query with the default setting.

regards,

Liviu


"Nested Loop Left Join  (cost=23.35..1965.46 rows=1 width=125) (actual time=50.408..231926.123 rows=2026 loops=1)"
"  Join Filter: (n.nodeid = public.nodeattributes.nodeid)"
"  ->  Nested Loop Left Join  (cost=17.81..1357.58 rows=1 width=115) (actual time=47.103..156521.050 rows=2026
loops=1)"
"        Join Filter: (n.nodeid = public.nodeattributes.nodeid)"
"        ->  Nested Loop Left Join  (cost=12.30..752.97 rows=1 width=105) (actual time=43.924..81977.726 rows=2026
loops=1)"
"              Join Filter: (n.nodeid = public.nodeattributes.nodeid)"
"              ->  Nested Loop Left Join  (cost=6.83..150.65 rows=1 width=95) (actual time=40.603..12477.227 rows=2026
loops=1)"
"                    ->  Nested Loop Left Join  (cost=6.83..150.37 rows=1 width=78) (actual time=38.448..12459.918
rows=2026loops=1)" 
"                          ->  Nested Loop Left Join  (cost=6.83..150.08 rows=1 width=70) (actual
time=31.793..12436.536rows=2026 loops=1)" 
"                                ->  Nested Loop Left Join  (cost=6.83..149.80 rows=1 width=62) (actual
time=6.588..12394.366rows=2026 loops=1)" 
"                                      Filter: (templatenodes.nodeid IS NULL)"
"                                      ->  Nested Loop Left Join  (cost=6.83..149.51 rows=1 width=62) (actual
time=6.525..12362.969rows=2206 loops=1)" 
"                                            Join Filter: (n.nodeid = rtunodes.nodeid)"
"                                            ->  Hash Left Join  (cost=2.44..63.29 rows=1 width=49) (actual
time=0.361..14.426rows=2206 loops=1)" 
"                                                  Hash Cond: (n.nodeid = templates.nodeid)"
"                                                  Filter: (templates.nodeid IS NULL)"
"                                                  ->  Seq Scan on nodes n  (cost=0.00..51.70 rows=2270 width=49)
(actualtime=0.071..4.417 rows=2270 loops=1)" 
"                                                  ->  Hash  (cost=1.64..1.64 rows=64 width=4) (actual
time=0.152..0.152rows=64 loops=1)" 
"                                                        ->  Seq Scan on templates  (cost=0.00..1.64 rows=64 width=4)
(actualtime=0.032..0.082 rows=64 loops=1)" 
"                                            ->  Hash Join  (cost=4.38..63.51 rows=1816 width=13) (actual
time=0.011..4.365rows=1816 loops=2206)" 
"                                                  Hash Cond: (rtunodes.rtuid = r.nodeid)"
"                                                  ->  Seq Scan on rtunodes  (cost=0.00..34.16 rows=1816 width=9)
(actualtime=0.008..1.276 rows=1816 loops=2206)" 
"                                                  ->  Hash  (cost=3.06..3.06 rows=106 width=8) (actual
time=0.241..0.241rows=106 loops=1)" 
"                                                        ->  Seq Scan on rtus r  (cost=0.00..3.06 rows=106 width=8)
(actualtime=0.029..0.136 rows=106 loops=1)" 
"                                      ->  Index Scan using nodeid_pkey on templatenodes  (cost=0.00..0.28 rows=1
width=4)(actual time=0.008..0.008 rows=0 loops=2206)" 
"                                            Index Cond: (n.nodeid = templatenodes.nodeid)"
"                                ->  Index Scan using rtus_pkey on rtus  (cost=0.00..0.27 rows=1 width=8) (actual
time=0.016..0.016rows=0 loops=2026)" 
"                                      Index Cond: (n.nodeid = rtus.nodeid)"
"                          ->  Index Scan using areas_pkey on areas  (cost=0.00..0.27 rows=1 width=8) (actual
time=0.007..0.007rows=0 loops=2026)" 
"                                Index Cond: (n.nodeid = areas.nodeid)"
"                    ->  Index Scan using realms_pkey on realms  (cost=0.00..0.27 rows=1 width=17) (actual
time=0.004..0.004rows=0 loops=2026)" 
"                          Index Cond: (n.nodeid = realms.nodeid)"
"              ->  Hash Join  (cost=5.48..600.38 rows=155 width=14) (actual time=0.812..34.198 rows=132 loops=2026)"
"                    Hash Cond: (public.nodeattributes.attributeid = a.attributeid)"
"                    ->  Seq Scan on nodeattributes  (cost=0.00..505.35 rows=23535 width=18) (actual time=0.009..16.660
rows=23535loops=2026)" 
"                    ->  Hash  (cost=5.47..5.47 rows=1 width=4) (actual time=0.196..0.196 rows=2 loops=1)"
"                          ->  Hash Join  (cost=1.18..5.47 rows=1 width=4) (actual time=0.124..0.187 rows=2 loops=1)"
"                                Hash Cond: (a.typeid = t.typeid)"
"                                ->  Seq Scan on attributes a  (cost=0.00..4.28 rows=2 width=8) (actual
time=0.044..0.103rows=2 loops=1)" 
"                                      Filter: ((name)::text = 'addUPIsubclass'::text)"
"                                ->  Hash  (cost=1.10..1.10 rows=6 width=4) (actual time=0.047..0.047 rows=6 loops=1)"
"                                      ->  Seq Scan on types t  (cost=0.00..1.10 rows=6 width=4) (actual
time=0.028..0.034rows=6 loops=1)" 
"                                            Filter: isattributetype"
"        ->  Hash Join  (cost=5.51..601.70 rows=233 width=14) (actual time=0.103..35.496 rows=1865 loops=2026)"
"              Hash Cond: (public.nodeattributes.attributeid = a.attributeid)"
"              ->  Seq Scan on nodeattributes  (cost=0.00..505.35 rows=23535 width=18) (actual time=0.009..16.595
rows=23535loops=2026)" 
"              ->  Hash  (cost=5.48..5.48 rows=2 width=4) (actual time=0.116..0.116 rows=3 loops=1)"
"                    ->  Hash Join  (cost=1.18..5.48 rows=2 width=4) (actual time=0.063..0.107 rows=3 loops=1)"
"                          Hash Cond: (a.typeid = t.typeid)"
"                          ->  Seq Scan on attributes a  (cost=0.00..4.28 rows=3 width=8) (actual time=0.017..0.056
rows=3loops=1)" 
"                                Filter: ((name)::text = 'addUPItemplate'::text)"
"                          ->  Hash  (cost=1.10..1.10 rows=6 width=4) (actual time=0.022..0.022 rows=6 loops=1)"
"                                ->  Seq Scan on types t  (cost=0.00..1.10 rows=6 width=4) (actual time=0.004..0.010
rows=6loops=1)" 
"                                      Filter: isattributetype"
"  ->  Hash Join  (cost=5.54..603.02 rows=388 width=14) (actual time=0.031..35.795 rows=2079 loops=2026)"
"        Hash Cond: (public.nodeattributes.attributeid = a.attributeid)"
"        ->  Seq Scan on nodeattributes  (cost=0.00..505.35 rows=23535 width=18) (actual time=0.008..16.766 rows=23535
loops=2026)"
"        ->  Hash  (cost=5.50..5.50 rows=3 width=4) (actual time=0.120..0.120 rows=5 loops=1)"
"              ->  Hash Join  (cost=1.18..5.50 rows=3 width=4) (actual time=0.074..0.110 rows=5 loops=1)"
"                    Hash Cond: (a.typeid = t.typeid)"
"                    ->  Seq Scan on attributes a  (cost=0.00..4.28 rows=5 width=8) (actual time=0.025..0.050 rows=5
loops=1)"
"                          Filter: ((name)::text = 'iconName'::text)"
"                    ->  Hash  (cost=1.10..1.10 rows=6 width=4) (actual time=0.026..0.026 rows=6 loops=1)"
"                          ->  Seq Scan on types t  (cost=0.00..1.10 rows=6 width=4) (actual time=0.004..0.010 rows=6
loops=1)"
"                                Filter: isattributetype"
"Total runtime: 231929.656 ms"