pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Liviu Ionescu <ilgb@livius.net>
To: pgsql-performance@postgresql.org
Subject: performance drop on 8.2.4, reverting to 8.1.4
Date: Fri, 18 May 2007 12:02:44 +0300
Message-ID: <006501c7992b$4d29ffb0$653e10ac@ilgvaio> (raw )
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)"
view thread (24+ messages) latest in thread
Message-ID: <006501c7992b$4d29ffb0$653e10ac@ilgvaio>
Permalink: ../006501c7992b$4d29ffb0$653e10ac@ilgvaio/
Also on: postgresql.org/message-id/006501c7992b$4d29ffb0$653e10ac@ilgvaio
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ilgb@livius.net
Subject: Re: performance drop on 8.2.4, reverting to 8.1.4
In-Reply-To: <006501c7992b$4d29ffb0$653e10ac@ilgvaio>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox