Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id A15679FB61C for ; Fri, 18 May 2007 06:21:52 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 14296-02-2 for ; Fri, 18 May 2007 06:21:48 -0300 (ADT) X-Greylist: delayed 00:17:54.35578 by SQLgrey-1.7.5 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id B022A9FC161 for ; Fri, 18 May 2007 06:21:47 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mail.livius.net (mail.livius.net [80.86.96.82]) by svr4.postgresql.org (Postfix) with ESMTP id CD1EF5AF893 for ; Fri, 18 May 2007 09:03:48 +0000 (GMT) Received: from ilgvaio (86-120-229-138.rdsnet.ro [86.120.229.138] (may be forged)) (authenticated bits=0) by mail.livius.net (8.12.5/8.12.5) with ESMTP id l4I93VeF011784 for ; Fri, 18 May 2007 12:03:32 +0300 From: "Liviu Ionescu" To: 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> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6822 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 Thread-Index: AceZKuulGj6cU9yPQDOuJYbho5UbSQ== Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200705/312 X-Sequence-Number: 24702 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,=20 CASE WHEN n.parentnodeid IS NULL THEN -1 ELSE n.parentnodeid END AS parentnodeid, n.nodename, av.value AS iconname,=20 avt.value AS templatename, avs.value AS subclass, = n.globalnodeid, n.isaddupi,=20 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,=20 CASE WHEN n.nodeclass::text =3D 'area'::text AND n.nodesubclass = IS NOT NULL THEN true ELSE false END AS istemplate,=20 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 =3D realms.nodeid LEFT JOIN areas ON n.nodeid =3D areas.nodeid LEFT JOIN rtus ON n.nodeid =3D rtus.nodeid LEFT JOIN templates ON n.nodeid =3D templates.nodeid LEFT JOIN templatenodes ON n.nodeid =3D templatenodes.nodeid LEFT JOIN (rtunodes JOIN rtus r ON rtunodes.rtuid =3D r.nodeid) ON n.nodeid =3D = rtunodes.nodeid LEFT JOIN ( SELECT attributes_values2_view.nodeid, = attributes_values2_view.value FROM attributes_values2_view WHERE attributes_values2_view.attributename::text =3D = 'iconName'::text) av ON n.nodeid =3D av.nodeid LEFT JOIN ( SELECT attributes_values2_view.nodeid, = attributes_values2_view.value FROM attributes_values2_view WHERE attributes_values2_view.attributename::text =3D = 'addUPItemplate'::text) avt ON n.nodeid =3D avt.nodeid LEFT JOIN ( SELECT attributes_values2_view.nodeid, = attributes_values2_view.value FROM attributes_values2_view WHERE attributes_values2_view.attributename::text =3D = 'addUPIsubclass'::text) avs ON n.nodeid =3D avs.nodeid WHERE templates.nodeid IS NULL AND templatenodes.nodeid IS NULL; CREATE OR REPLACE VIEW attributes_values2_view AS=20 SELECT nodeattributes.nodeid, nodeattributes.attributeid, a.name AS = attributename,=20 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 =3D 1 (with default it was = worse, full of nested loops) "Nested Loop Left Join (cost=3D32.01..2012.31 rows=3D1 width=3D230)" " Join Filter: (n.nodeid =3D public.nodeattributes.nodeid)" " -> Nested Loop Left Join (cost=3D26.47..1411.38 rows=3D1 = width=3D220)" " Join Filter: (n.nodeid =3D public.nodeattributes.nodeid)" " -> Nested Loop Left Join (cost=3D20.93..810.45 rows=3D1 = width=3D210)" " Join Filter: (n.nodeid =3D public.nodeattributes.nodeid)" " -> Nested Loop Left Join (cost=3D15.39..209.52 rows=3D1 = width=3D200)" " Join Filter: (n.nodeid =3D rtunodes.nodeid)" " -> Nested Loop Left Join (cost=3D11.14..122.60 = rows=3D1 width=3D187)" " Filter: (templatenodes.nodeid IS NULL)" " -> Hash Left Join (cost=3D11.14..99.52 = rows=3D11 width=3D187)" " Hash Cond: (n.nodeid =3D = templates.nodeid)" " Filter: (templates.nodeid IS NULL)" " -> Hash Left Join (cost=3D8.70..87.95 = rows=3D2266 width=3D187)" " Hash Cond: (n.nodeid =3D = rtus.nodeid)" " -> Hash Left Join = (cost=3D4.45..74.20 rows=3D2266 width=3D179)" " Hash Cond: (n.nodeid =3D = areas.nodeid)" " -> Hash Left Join = (cost=3D1.45..61.81 rows=3D2266 width=3D171)" " Hash Cond: (n.nodeid = =3D realms.nodeid)" " -> Seq Scan on nodes = n (cost=3D0.00..51.66 rows=3D2266 width=3D49)" " -> Hash = (cost=3D1.20..1.20 rows=3D20 width=3D122)" " -> Seq Scan on = realms (cost=3D0.00..1.20 rows=3D20 width=3D122)" " -> Hash = (cost=3D1.89..1.89 rows=3D89 width=3D8)" " -> Seq Scan on areas = (cost=3D0.00..1.89 rows=3D89 width=3D8)" " -> Hash (cost=3D3.00..3.00 = rows=3D100 width=3D8)" " -> Seq Scan on rtus = (cost=3D0.00..3.00 rows=3D100 width=3D8)" " -> Hash (cost=3D1.64..1.64 rows=3D64 = width=3D4)" " -> Seq Scan on templates = (cost=3D0.00..1.64 rows=3D64 width=3D4)" " -> Index Scan using nodeid_pkey on = templatenodes (cost=3D0.00..2.09 rows=3D1 width=3D4)" " Index Cond: (n.nodeid =3D = templatenodes.nodeid)" " -> Hash Join (cost=3D4.25..63.93 rows=3D1839 = width=3D13)" " Hash Cond: (rtunodes.rtuid =3D r.nodeid)" " -> Seq Scan on rtunodes (cost=3D0.00..34.39 = rows=3D1839 width=3D9)" " -> Hash (cost=3D3.00..3.00 rows=3D100 = width=3D8)" " -> Seq Scan on rtus r = (cost=3D0.00..3.00 rows=3D100 width=3D8)" " -> Hash Join (cost=3D5.54..600.89 rows=3D3 width=3D14)" " Hash Cond: (a.typeid =3D t.typeid)" " -> Hash Join (cost=3D4.38..599.23 rows=3D125 = width=3D18)" " Hash Cond: (public.nodeattributes.attributeid = =3D a.attributeid)" " -> Seq Scan on nodeattributes = (cost=3D0.00..505.35 rows=3D23535 width=3D18)" " -> Hash (cost=3D4.36..4.36 rows=3D1 = width=3D8)" " -> Seq Scan on attributes a = (cost=3D0.00..4.36 rows=3D1 width=3D8)" " Filter: ((name)::text =3D = 'iconName'::text)" " -> Hash (cost=3D1.10..1.10 rows=3D5 width=3D4)" " -> Seq Scan on types t (cost=3D0.00..1.10 = rows=3D5 width=3D4)" " Filter: isattributetype" " -> Hash Join (cost=3D5.54..600.89 rows=3D3 width=3D14)" " Hash Cond: (a.typeid =3D t.typeid)" " -> Hash Join (cost=3D4.38..599.23 rows=3D125 = width=3D18)" " Hash Cond: (public.nodeattributes.attributeid =3D = a.attributeid)" " -> Seq Scan on nodeattributes = (cost=3D0.00..505.35 rows=3D23535 width=3D18)" " -> Hash (cost=3D4.36..4.36 rows=3D1 width=3D8)" " -> Seq Scan on attributes a = (cost=3D0.00..4.36 rows=3D1 width=3D8)" " Filter: ((name)::text =3D = 'addUPItemplate'::text)" " -> Hash (cost=3D1.10..1.10 rows=3D5 width=3D4)" " -> Seq Scan on types t (cost=3D0.00..1.10 = rows=3D5 width=3D4)" " Filter: isattributetype" " -> Hash Join (cost=3D5.54..600.89 rows=3D3 width=3D14)" " Hash Cond: (a.typeid =3D t.typeid)" " -> Hash Join (cost=3D4.38..599.23 rows=3D125 width=3D18)" " Hash Cond: (public.nodeattributes.attributeid =3D = a.attributeid)" " -> Seq Scan on nodeattributes (cost=3D0.00..505.35 = rows=3D23535 width=3D18)" " -> Hash (cost=3D4.36..4.36 rows=3D1 width=3D8)" " -> Seq Scan on attributes a (cost=3D0.00..4.36 = rows=3D1 width=3D8)" " Filter: ((name)::text =3D = 'addUPIsubclass'::text)" " -> Hash (cost=3D1.10..1.10 rows=3D5 width=3D4)" " -> Seq Scan on types t (cost=3D0.00..1.10 rows=3D5 = width=3D4)" " Filter: isattributetype" the 8.1.4 plan "Hash Left Join (cost=3D1587.19..1775.85 rows=3D2270 width=3D230)" " Hash Cond: ("outer".nodeid =3D "inner".nodeid)" " -> Hash Left Join (cost=3D1086.04..1257.64 rows=3D2270 = width=3D220)" " Hash Cond: ("outer".nodeid =3D "inner".nodeid)" " -> Hash Left Join (cost=3D584.89..745.10 rows=3D2270 = width=3D210)" " Hash Cond: ("outer".nodeid =3D "inner".nodeid)" " -> Hash Left Join (cost=3D83.74..232.55 rows=3D2270 = width=3D200)" " Hash Cond: ("outer".nodeid =3D "inner".nodeid)" " -> Hash Left Join (cost=3D14.47..128.10 = rows=3D2270 width=3D187)" " Hash Cond: ("outer".nodeid =3D = "inner".nodeid)" " Filter: ("inner".nodeid IS NULL)" " -> Hash Left Join (cost=3D8.43..108.26 = rows=3D2270 width=3D187)" " Hash Cond: ("outer".nodeid =3D = "inner".nodeid)" " Filter: ("inner".nodeid IS NULL)" " -> Hash Left Join (cost=3D6.62..94.47 = rows=3D2270 width=3D187)" " Hash Cond: ("outer".nodeid =3D = "inner".nodeid)" " -> Hash Left Join = (cost=3D3.30..78.74 rows=3D2270 width=3D179)" " Hash Cond: ("outer".nodeid = =3D "inner".nodeid)" " -> Hash Left Join = (cost=3D1.24..64.48 rows=3D2270 width=3D171)" " Hash Cond: = ("outer".nodeid =3D "inner".nodeid)" " -> Seq Scan on nodes = n (cost=3D0.00..51.70 rows=3D2270 width=3D49)" " -> Hash = (cost=3D1.19..1.19 rows=3D19 width=3D122)" " -> Seq Scan on = realms (cost=3D0.00..1.19 rows=3D19 width=3D122)" " -> Hash = (cost=3D1.85..1.85 rows=3D85 width=3D8)" " -> Seq Scan on areas = (cost=3D0.00..1.85 rows=3D85 width=3D8)" " -> Hash (cost=3D3.06..3.06 = rows=3D106 width=3D8)" " -> Seq Scan on rtus = (cost=3D0.00..3.06 rows=3D106 width=3D8)" " -> Hash (cost=3D1.64..1.64 rows=3D64 = width=3D4)" " -> Seq Scan on templates = (cost=3D0.00..1.64 rows=3D64 width=3D4)" " -> Hash (cost=3D5.44..5.44 rows=3D244 = width=3D4)" " -> Seq Scan on templatenodes = (cost=3D0.00..5.44 rows=3D244 width=3D4)" " -> Hash (cost=3D64.72..64.72 rows=3D1816 = width=3D13)" " -> Hash Join (cost=3D3.33..64.72 = rows=3D1816 width=3D13)" " Hash Cond: ("outer".rtuid =3D = "inner".nodeid)" " -> Seq Scan on rtunodes = (cost=3D0.00..34.16 rows=3D1816 width=3D9)" " -> Hash (cost=3D3.06..3.06 rows=3D106 = width=3D8)" " -> Seq Scan on rtus r = (cost=3D0.00..3.06 rows=3D106 width=3D8)" " -> Hash (cost=3D501.14..501.14 rows=3D4 width=3D14)" " -> Nested Loop (cost=3D207.37..501.14 rows=3D4 = width=3D14)" " -> Nested Loop (cost=3D0.00..5.44 rows=3D1 = width=3D4)" " Join Filter: ("outer".typeid =3D = "inner".typeid)" " -> Seq Scan on attributes a = (cost=3D0.00..4.28 rows=3D1 width=3D8)" " Filter: ((name)::text =3D = 'iconName'::text)" " -> Seq Scan on types t = (cost=3D0.00..1.10 rows=3D5 width=3D4)" " Filter: isattributetype" " -> Bitmap Heap Scan on nodeattributes = (cost=3D207.37..493.33 rows=3D190 width=3D18)" " Recheck Cond: = (nodeattributes.attributeid =3D "outer".attributeid)" " -> Bitmap Index Scan on = nodeattributes_pkey (cost=3D0.00..207.37 rows=3D190 width=3D0)" " Index Cond: = (nodeattributes.attributeid =3D "outer".attributeid)" " -> Hash (cost=3D501.14..501.14 rows=3D4 width=3D14)" " -> Nested Loop (cost=3D207.37..501.14 rows=3D4 = width=3D14)" " -> Nested Loop (cost=3D0.00..5.44 rows=3D1 = width=3D4)" " Join Filter: ("outer".typeid =3D = "inner".typeid)" " -> Seq Scan on attributes a = (cost=3D0.00..4.28 rows=3D1 width=3D8)" " Filter: ((name)::text =3D = 'addUPItemplate'::text)" " -> Seq Scan on types t (cost=3D0.00..1.10 = rows=3D5 width=3D4)" " Filter: isattributetype" " -> Bitmap Heap Scan on nodeattributes = (cost=3D207.37..493.33 rows=3D190 width=3D18)" " Recheck Cond: (nodeattributes.attributeid =3D = "outer".attributeid)" " -> Bitmap Index Scan on nodeattributes_pkey = (cost=3D0.00..207.37 rows=3D190 width=3D0)" " Index Cond: (nodeattributes.attributeid = =3D "outer".attributeid)" " -> Hash (cost=3D501.14..501.14 rows=3D4 width=3D14)" " -> Nested Loop (cost=3D207.37..501.14 rows=3D4 width=3D14)" " -> Nested Loop (cost=3D0.00..5.44 rows=3D1 width=3D4)" " Join Filter: ("outer".typeid =3D "inner".typeid)" " -> Seq Scan on attributes a (cost=3D0.00..4.28 = rows=3D1 width=3D8)" " Filter: ((name)::text =3D = 'addUPIsubclass'::text)" " -> Seq Scan on types t (cost=3D0.00..1.10 = rows=3D5 width=3D4)" " Filter: isattributetype" " -> Bitmap Heap Scan on nodeattributes = (cost=3D207.37..493.33 rows=3D190 width=3D18)" " Recheck Cond: (nodeattributes.attributeid =3D = "outer".attributeid)" " -> Bitmap Index Scan on nodeattributes_pkey = (cost=3D0.00..207.37 rows=3D190 width=3D0)" " Index Cond: (nodeattributes.attributeid =3D = "outer".attributeid)"=20