performance drop on 8.2.4, reverting to 8.1.4 - Mailing list pgsql-performance

I recently tried to upgrade to 8.2.4, but major queries I wrote for 8.1.4 are now planned differently on 8.2.4 and are
nolonger usable. What the 8.1.4 planned as a series of 'hash left join's and took about 2 seconds now is planned as
'nestedloop left joins' and takes forever. 

Other request were also affected, increasing the time form miliseconds to hundreds of miliseconds, even seconds.

The worst performance hit was on the following query. I know it is a bit extreme, but worked perfectly on 8.1.4.

Regards,

Liviu


SELECT n.nodeid,
        CASE
            WHEN n.parentnodeid IS NULL THEN -1
            ELSE n.parentnodeid
        END AS parentnodeid, n.nodename, av.value AS iconname,
        avt.value AS templatename, avs.value AS subclass, n.globalnodeid, n.isaddupi,
        CASE
            WHEN realms.nodeid IS NOT NULL THEN 'SERVER'::text
            WHEN areas.nodeid IS NOT NULL THEN 'AREA'::text
            WHEN rtus.nodeid IS NOT NULL THEN 'DEVICE'::text
            WHEN rtunodes.nodeid IS NOT NULL THEN 'TAG'::text
            ELSE NULL::text
        END AS "class", realms.name AS realmname,
        CASE
            WHEN n.nodeclass::text = 'area'::text AND n.nodesubclass IS NOT NULL THEN true
            ELSE false
        END AS istemplate,
        CASE
            WHEN realms.nodeid IS NOT NULL THEN realms.nodeid
            WHEN areas.nodeid IS NOT NULL THEN areas.realmid
            WHEN rtus.nodeid IS NOT NULL THEN rtus.realmid
            WHEN rtunodes.nodeid IS NOT NULL THEN r.realmid
            ELSE NULL::integer
        END AS realmid, rtunodes.rtuid, rtunodes.isinvalid, n.isvalid
   FROM nodes n
   LEFT JOIN realms ON n.nodeid = realms.nodeid
   LEFT JOIN areas ON n.nodeid = areas.nodeid
   LEFT JOIN rtus ON n.nodeid = rtus.nodeid
   LEFT JOIN templates ON n.nodeid = templates.nodeid
   LEFT JOIN templatenodes ON n.nodeid = templatenodes.nodeid
   LEFT JOIN (rtunodes
   JOIN rtus r ON rtunodes.rtuid = r.nodeid) ON n.nodeid = rtunodes.nodeid
   LEFT JOIN ( SELECT attributes_values2_view.nodeid, attributes_values2_view.value
   FROM attributes_values2_view
  WHERE attributes_values2_view.attributename::text = 'iconName'::text) av ON n.nodeid = av.nodeid
   LEFT JOIN ( SELECT attributes_values2_view.nodeid, attributes_values2_view.value
   FROM attributes_values2_view
  WHERE attributes_values2_view.attributename::text = 'addUPItemplate'::text) avt ON n.nodeid = avt.nodeid
   LEFT JOIN ( SELECT attributes_values2_view.nodeid, attributes_values2_view.value
   FROM attributes_values2_view
  WHERE attributes_values2_view.attributename::text = 'addUPIsubclass'::text) avs ON n.nodeid = avs.nodeid
  WHERE templates.nodeid IS NULL AND templatenodes.nodeid IS NULL;


CREATE OR REPLACE VIEW attributes_values2_view AS
 SELECT nodeattributes.nodeid, nodeattributes.attributeid, a.name AS attributename,
   t.name AS typename, a.typeid, a.valuesize, a.flags, nodeattributes.value, a.creationdate
   FROM nodeattributes
   LEFT JOIN attributes a USING (attributeid)
   LEFT JOIN types t USING (typeid)
  WHERE t.isattributetype;



the 8.2.4 plan with join_collapse_limit = 1 (with default it was worse, full of nested loops)

"Nested Loop Left Join  (cost=32.01..2012.31 rows=1 width=230)"
"  Join Filter: (n.nodeid = public.nodeattributes.nodeid)"
"  ->  Nested Loop Left Join  (cost=26.47..1411.38 rows=1 width=220)"
"        Join Filter: (n.nodeid = public.nodeattributes.nodeid)"
"        ->  Nested Loop Left Join  (cost=20.93..810.45 rows=1 width=210)"
"              Join Filter: (n.nodeid = public.nodeattributes.nodeid)"
"              ->  Nested Loop Left Join  (cost=15.39..209.52 rows=1 width=200)"
"                    Join Filter: (n.nodeid = rtunodes.nodeid)"
"                    ->  Nested Loop Left Join  (cost=11.14..122.60 rows=1 width=187)"
"                          Filter: (templatenodes.nodeid IS NULL)"
"                          ->  Hash Left Join  (cost=11.14..99.52 rows=11 width=187)"
"                                Hash Cond: (n.nodeid = templates.nodeid)"
"                                Filter: (templates.nodeid IS NULL)"
"                                ->  Hash Left Join  (cost=8.70..87.95 rows=2266 width=187)"
"                                      Hash Cond: (n.nodeid = rtus.nodeid)"
"                                      ->  Hash Left Join  (cost=4.45..74.20 rows=2266 width=179)"
"                                            Hash Cond: (n.nodeid = areas.nodeid)"
"                                            ->  Hash Left Join  (cost=1.45..61.81 rows=2266 width=171)"
"                                                  Hash Cond: (n.nodeid = realms.nodeid)"
"                                                  ->  Seq Scan on nodes n  (cost=0.00..51.66 rows=2266 width=49)"
"                                                  ->  Hash  (cost=1.20..1.20 rows=20 width=122)"
"                                                        ->  Seq Scan on realms  (cost=0.00..1.20 rows=20 width=122)"
"                                            ->  Hash  (cost=1.89..1.89 rows=89 width=8)"
"                                                  ->  Seq Scan on areas  (cost=0.00..1.89 rows=89 width=8)"
"                                      ->  Hash  (cost=3.00..3.00 rows=100 width=8)"
"                                            ->  Seq Scan on rtus  (cost=0.00..3.00 rows=100 width=8)"
"                                ->  Hash  (cost=1.64..1.64 rows=64 width=4)"
"                                      ->  Seq Scan on templates  (cost=0.00..1.64 rows=64 width=4)"
"                          ->  Index Scan using nodeid_pkey on templatenodes  (cost=0.00..2.09 rows=1 width=4)"
"                                Index Cond: (n.nodeid = templatenodes.nodeid)"
"                    ->  Hash Join  (cost=4.25..63.93 rows=1839 width=13)"
"                          Hash Cond: (rtunodes.rtuid = r.nodeid)"
"                          ->  Seq Scan on rtunodes  (cost=0.00..34.39 rows=1839 width=9)"
"                          ->  Hash  (cost=3.00..3.00 rows=100 width=8)"
"                                ->  Seq Scan on rtus r  (cost=0.00..3.00 rows=100 width=8)"
"              ->  Hash Join  (cost=5.54..600.89 rows=3 width=14)"
"                    Hash Cond: (a.typeid = t.typeid)"
"                    ->  Hash Join  (cost=4.38..599.23 rows=125 width=18)"
"                          Hash Cond: (public.nodeattributes.attributeid = a.attributeid)"
"                          ->  Seq Scan on nodeattributes  (cost=0.00..505.35 rows=23535 width=18)"
"                          ->  Hash  (cost=4.36..4.36 rows=1 width=8)"
"                                ->  Seq Scan on attributes a  (cost=0.00..4.36 rows=1 width=8)"
"                                      Filter: ((name)::text = 'iconName'::text)"
"                    ->  Hash  (cost=1.10..1.10 rows=5 width=4)"
"                          ->  Seq Scan on types t  (cost=0.00..1.10 rows=5 width=4)"
"                                Filter: isattributetype"
"        ->  Hash Join  (cost=5.54..600.89 rows=3 width=14)"
"              Hash Cond: (a.typeid = t.typeid)"
"              ->  Hash Join  (cost=4.38..599.23 rows=125 width=18)"
"                    Hash Cond: (public.nodeattributes.attributeid = a.attributeid)"
"                    ->  Seq Scan on nodeattributes  (cost=0.00..505.35 rows=23535 width=18)"
"                    ->  Hash  (cost=4.36..4.36 rows=1 width=8)"
"                          ->  Seq Scan on attributes a  (cost=0.00..4.36 rows=1 width=8)"
"                                Filter: ((name)::text = 'addUPItemplate'::text)"
"              ->  Hash  (cost=1.10..1.10 rows=5 width=4)"
"                    ->  Seq Scan on types t  (cost=0.00..1.10 rows=5 width=4)"
"                          Filter: isattributetype"
"  ->  Hash Join  (cost=5.54..600.89 rows=3 width=14)"
"        Hash Cond: (a.typeid = t.typeid)"
"        ->  Hash Join  (cost=4.38..599.23 rows=125 width=18)"
"              Hash Cond: (public.nodeattributes.attributeid = a.attributeid)"
"              ->  Seq Scan on nodeattributes  (cost=0.00..505.35 rows=23535 width=18)"
"              ->  Hash  (cost=4.36..4.36 rows=1 width=8)"
"                    ->  Seq Scan on attributes a  (cost=0.00..4.36 rows=1 width=8)"
"                          Filter: ((name)::text = 'addUPIsubclass'::text)"
"        ->  Hash  (cost=1.10..1.10 rows=5 width=4)"
"              ->  Seq Scan on types t  (cost=0.00..1.10 rows=5 width=4)"
"                    Filter: isattributetype"



the 8.1.4 plan

"Hash Left Join  (cost=1587.19..1775.85 rows=2270 width=230)"
"  Hash Cond: ("outer".nodeid = "inner".nodeid)"
"  ->  Hash Left Join  (cost=1086.04..1257.64 rows=2270 width=220)"
"        Hash Cond: ("outer".nodeid = "inner".nodeid)"
"        ->  Hash Left Join  (cost=584.89..745.10 rows=2270 width=210)"
"              Hash Cond: ("outer".nodeid = "inner".nodeid)"
"              ->  Hash Left Join  (cost=83.74..232.55 rows=2270 width=200)"
"                    Hash Cond: ("outer".nodeid = "inner".nodeid)"
"                    ->  Hash Left Join  (cost=14.47..128.10 rows=2270 width=187)"
"                          Hash Cond: ("outer".nodeid = "inner".nodeid)"
"                          Filter: ("inner".nodeid IS NULL)"
"                          ->  Hash Left Join  (cost=8.43..108.26 rows=2270 width=187)"
"                                Hash Cond: ("outer".nodeid = "inner".nodeid)"
"                                Filter: ("inner".nodeid IS NULL)"
"                                ->  Hash Left Join  (cost=6.62..94.47 rows=2270 width=187)"
"                                      Hash Cond: ("outer".nodeid = "inner".nodeid)"
"                                      ->  Hash Left Join  (cost=3.30..78.74 rows=2270 width=179)"
"                                            Hash Cond: ("outer".nodeid = "inner".nodeid)"
"                                            ->  Hash Left Join  (cost=1.24..64.48 rows=2270 width=171)"
"                                                  Hash Cond: ("outer".nodeid = "inner".nodeid)"
"                                                  ->  Seq Scan on nodes n  (cost=0.00..51.70 rows=2270 width=49)"
"                                                  ->  Hash  (cost=1.19..1.19 rows=19 width=122)"
"                                                        ->  Seq Scan on realms  (cost=0.00..1.19 rows=19 width=122)"
"                                            ->  Hash  (cost=1.85..1.85 rows=85 width=8)"
"                                                  ->  Seq Scan on areas  (cost=0.00..1.85 rows=85 width=8)"
"                                      ->  Hash  (cost=3.06..3.06 rows=106 width=8)"
"                                            ->  Seq Scan on rtus  (cost=0.00..3.06 rows=106 width=8)"
"                                ->  Hash  (cost=1.64..1.64 rows=64 width=4)"
"                                      ->  Seq Scan on templates  (cost=0.00..1.64 rows=64 width=4)"
"                          ->  Hash  (cost=5.44..5.44 rows=244 width=4)"
"                                ->  Seq Scan on templatenodes  (cost=0.00..5.44 rows=244 width=4)"
"                    ->  Hash  (cost=64.72..64.72 rows=1816 width=13)"
"                          ->  Hash Join  (cost=3.33..64.72 rows=1816 width=13)"
"                                Hash Cond: ("outer".rtuid = "inner".nodeid)"
"                                ->  Seq Scan on rtunodes  (cost=0.00..34.16 rows=1816 width=9)"
"                                ->  Hash  (cost=3.06..3.06 rows=106 width=8)"
"                                      ->  Seq Scan on rtus r  (cost=0.00..3.06 rows=106 width=8)"
"              ->  Hash  (cost=501.14..501.14 rows=4 width=14)"
"                    ->  Nested Loop  (cost=207.37..501.14 rows=4 width=14)"
"                          ->  Nested Loop  (cost=0.00..5.44 rows=1 width=4)"
"                                Join Filter: ("outer".typeid = "inner".typeid)"
"                                ->  Seq Scan on attributes a  (cost=0.00..4.28 rows=1 width=8)"
"                                      Filter: ((name)::text = 'iconName'::text)"
"                                ->  Seq Scan on types t  (cost=0.00..1.10 rows=5 width=4)"
"                                      Filter: isattributetype"
"                          ->  Bitmap Heap Scan on nodeattributes  (cost=207.37..493.33 rows=190 width=18)"
"                                Recheck Cond: (nodeattributes.attributeid = "outer".attributeid)"
"                                ->  Bitmap Index Scan on nodeattributes_pkey  (cost=0.00..207.37 rows=190 width=0)"
"                                      Index Cond: (nodeattributes.attributeid = "outer".attributeid)"
"        ->  Hash  (cost=501.14..501.14 rows=4 width=14)"
"              ->  Nested Loop  (cost=207.37..501.14 rows=4 width=14)"
"                    ->  Nested Loop  (cost=0.00..5.44 rows=1 width=4)"
"                          Join Filter: ("outer".typeid = "inner".typeid)"
"                          ->  Seq Scan on attributes a  (cost=0.00..4.28 rows=1 width=8)"
"                                Filter: ((name)::text = 'addUPItemplate'::text)"
"                          ->  Seq Scan on types t  (cost=0.00..1.10 rows=5 width=4)"
"                                Filter: isattributetype"
"                    ->  Bitmap Heap Scan on nodeattributes  (cost=207.37..493.33 rows=190 width=18)"
"                          Recheck Cond: (nodeattributes.attributeid = "outer".attributeid)"
"                          ->  Bitmap Index Scan on nodeattributes_pkey  (cost=0.00..207.37 rows=190 width=0)"
"                                Index Cond: (nodeattributes.attributeid = "outer".attributeid)"
"  ->  Hash  (cost=501.14..501.14 rows=4 width=14)"
"        ->  Nested Loop  (cost=207.37..501.14 rows=4 width=14)"
"              ->  Nested Loop  (cost=0.00..5.44 rows=1 width=4)"
"                    Join Filter: ("outer".typeid = "inner".typeid)"
"                    ->  Seq Scan on attributes a  (cost=0.00..4.28 rows=1 width=8)"
"                          Filter: ((name)::text = 'addUPIsubclass'::text)"
"                    ->  Seq Scan on types t  (cost=0.00..1.10 rows=5 width=4)"
"                          Filter: isattributetype"
"              ->  Bitmap Heap Scan on nodeattributes  (cost=207.37..493.33 rows=190 width=18)"
"                    Recheck Cond: (nodeattributes.attributeid = "outer".attributeid)"
"                    ->  Bitmap Index Scan on nodeattributes_pkey  (cost=0.00..207.37 rows=190 width=0)"
"                          Index Cond: (nodeattributes.attributeid = "outer".attributeid)"