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 no longer usable. What the 8.1.4
planned as a series of 'hash left join's and took about 2 seconds now is
planned as 'nested loop 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)"
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
