Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 3E94D9FB841 for ; Fri, 18 May 2007 08:06:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 67314-01 for ; Fri, 18 May 2007 08:06:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.livius.net (mail.livius.net [80.86.96.82]) by postgresql.org (Postfix) with ESMTP id D64369FB32C for ; Fri, 18 May 2007 08:06:25 -0300 (ADT) 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 l4IB6NeF011969 for ; Fri, 18 May 2007 14:06:23 +0300 From: "Liviu Ionescu" To: Subject: Re: performance drop on 8.2.4, reverting to 8.1.4 Date: Fri, 18 May 2007 14:05:36 +0300 Message-ID: <008201c7993c$76c88a10$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: AceZOoRKYTezIJSXQr2upusWs1BdrgAAX35Q In-Reply-To: <20070518104646.GA13811@uio.no> 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/316 X-Sequence-Number: 24706 > Is this with the join collapse limit set to 1, or with=20 > default? (Default is generally more interesting.) below is the same query with the default setting. regards, Liviu "Nested Loop Left Join (cost=3D23.35..1965.46 rows=3D1 width=3D125) = (actual time=3D50.408..231926.123 rows=3D2026 loops=3D1)" " Join Filter: (n.nodeid =3D public.nodeattributes.nodeid)" " -> Nested Loop Left Join (cost=3D17.81..1357.58 rows=3D1 = width=3D115) (actual time=3D47.103..156521.050 rows=3D2026 loops=3D1)" " Join Filter: (n.nodeid =3D public.nodeattributes.nodeid)" " -> Nested Loop Left Join (cost=3D12.30..752.97 rows=3D1 = width=3D105) (actual time=3D43.924..81977.726 rows=3D2026 loops=3D1)" " Join Filter: (n.nodeid =3D public.nodeattributes.nodeid)" " -> Nested Loop Left Join (cost=3D6.83..150.65 rows=3D1 = width=3D95) (actual time=3D40.603..12477.227 rows=3D2026 loops=3D1)" " -> Nested Loop Left Join (cost=3D6.83..150.37 = rows=3D1 width=3D78) (actual time=3D38.448..12459.918 rows=3D2026 = loops=3D1)" " -> Nested Loop Left Join = (cost=3D6.83..150.08 rows=3D1 width=3D70) (actual = time=3D31.793..12436.536 rows=3D2026 loops=3D1)" " -> Nested Loop Left Join = (cost=3D6.83..149.80 rows=3D1 width=3D62) (actual = time=3D6.588..12394.366 rows=3D2026 loops=3D1)" " Filter: (templatenodes.nodeid IS = NULL)" " -> Nested Loop Left Join = (cost=3D6.83..149.51 rows=3D1 width=3D62) (actual = time=3D6.525..12362.969 rows=3D2206 loops=3D1)" " Join Filter: (n.nodeid =3D = rtunodes.nodeid)" " -> Hash Left Join = (cost=3D2.44..63.29 rows=3D1 width=3D49) (actual time=3D0.361..14.426 = rows=3D2206 loops=3D1)" " Hash Cond: (n.nodeid = =3D templates.nodeid)" " Filter: = (templates.nodeid IS NULL)" " -> Seq Scan on nodes = n (cost=3D0.00..51.70 rows=3D2270 width=3D49) (actual = time=3D0.071..4.417 rows=3D2270 loops=3D1)" " -> Hash = (cost=3D1.64..1.64 rows=3D64 width=3D4) (actual time=3D0.152..0.152 = rows=3D64 loops=3D1)" " -> Seq Scan on = templates (cost=3D0.00..1.64 rows=3D64 width=3D4) (actual = time=3D0.032..0.082 rows=3D64 loops=3D1)" " -> Hash Join = (cost=3D4.38..63.51 rows=3D1816 width=3D13) (actual time=3D0.011..4.365 = rows=3D1816 loops=3D2206)" " Hash Cond: = (rtunodes.rtuid =3D r.nodeid)" " -> Seq Scan on = rtunodes (cost=3D0.00..34.16 rows=3D1816 width=3D9) (actual = time=3D0.008..1.276 rows=3D1816 loops=3D2206)" " -> Hash = (cost=3D3.06..3.06 rows=3D106 width=3D8) (actual time=3D0.241..0.241 = rows=3D106 loops=3D1)" " -> Seq Scan on = rtus r (cost=3D0.00..3.06 rows=3D106 width=3D8) (actual = time=3D0.029..0.136 rows=3D106 loops=3D1)" " -> Index Scan using nodeid_pkey = on templatenodes (cost=3D0.00..0.28 rows=3D1 width=3D4) (actual = time=3D0.008..0.008 rows=3D0 loops=3D2206)" " Index Cond: (n.nodeid =3D = templatenodes.nodeid)" " -> Index Scan using rtus_pkey on rtus = (cost=3D0.00..0.27 rows=3D1 width=3D8) (actual time=3D0.016..0.016 = rows=3D0 loops=3D2026)" " Index Cond: (n.nodeid =3D = rtus.nodeid)" " -> Index Scan using areas_pkey on areas = (cost=3D0.00..0.27 rows=3D1 width=3D8) (actual time=3D0.007..0.007 = rows=3D0 loops=3D2026)" " Index Cond: (n.nodeid =3D = areas.nodeid)" " -> Index Scan using realms_pkey on realms = (cost=3D0.00..0.27 rows=3D1 width=3D17) (actual time=3D0.004..0.004 = rows=3D0 loops=3D2026)" " Index Cond: (n.nodeid =3D realms.nodeid)" " -> Hash Join (cost=3D5.48..600.38 rows=3D155 = width=3D14) (actual time=3D0.812..34.198 rows=3D132 loops=3D2026)" " Hash Cond: (public.nodeattributes.attributeid =3D = a.attributeid)" " -> Seq Scan on nodeattributes = (cost=3D0.00..505.35 rows=3D23535 width=3D18) (actual = time=3D0.009..16.660 rows=3D23535 loops=3D2026)" " -> Hash (cost=3D5.47..5.47 rows=3D1 width=3D4) = (actual time=3D0.196..0.196 rows=3D2 loops=3D1)" " -> Hash Join (cost=3D1.18..5.47 rows=3D1 = width=3D4) (actual time=3D0.124..0.187 rows=3D2 loops=3D1)" " Hash Cond: (a.typeid =3D t.typeid)" " -> Seq Scan on attributes a = (cost=3D0.00..4.28 rows=3D2 width=3D8) (actual time=3D0.044..0.103 = rows=3D2 loops=3D1)" " Filter: ((name)::text =3D = 'addUPIsubclass'::text)" " -> Hash (cost=3D1.10..1.10 rows=3D6 = width=3D4) (actual time=3D0.047..0.047 rows=3D6 loops=3D1)" " -> Seq Scan on types t = (cost=3D0.00..1.10 rows=3D6 width=3D4) (actual time=3D0.028..0.034 = rows=3D6 loops=3D1)" " Filter: isattributetype" " -> Hash Join (cost=3D5.51..601.70 rows=3D233 width=3D14) = (actual time=3D0.103..35.496 rows=3D1865 loops=3D2026)" " Hash Cond: (public.nodeattributes.attributeid =3D = a.attributeid)" " -> Seq Scan on nodeattributes (cost=3D0.00..505.35 = rows=3D23535 width=3D18) (actual time=3D0.009..16.595 rows=3D23535 = loops=3D2026)" " -> Hash (cost=3D5.48..5.48 rows=3D2 width=3D4) (actual = time=3D0.116..0.116 rows=3D3 loops=3D1)" " -> Hash Join (cost=3D1.18..5.48 rows=3D2 = width=3D4) (actual time=3D0.063..0.107 rows=3D3 loops=3D1)" " Hash Cond: (a.typeid =3D t.typeid)" " -> Seq Scan on attributes a = (cost=3D0.00..4.28 rows=3D3 width=3D8) (actual time=3D0.017..0.056 = rows=3D3 loops=3D1)" " Filter: ((name)::text =3D = 'addUPItemplate'::text)" " -> Hash (cost=3D1.10..1.10 rows=3D6 = width=3D4) (actual time=3D0.022..0.022 rows=3D6 loops=3D1)" " -> Seq Scan on types t = (cost=3D0.00..1.10 rows=3D6 width=3D4) (actual time=3D0.004..0.010 = rows=3D6 loops=3D1)" " Filter: isattributetype" " -> Hash Join (cost=3D5.54..603.02 rows=3D388 width=3D14) (actual = time=3D0.031..35.795 rows=3D2079 loops=3D2026)" " Hash Cond: (public.nodeattributes.attributeid =3D = a.attributeid)" " -> Seq Scan on nodeattributes (cost=3D0.00..505.35 = rows=3D23535 width=3D18) (actual time=3D0.008..16.766 rows=3D23535 = loops=3D2026)" " -> Hash (cost=3D5.50..5.50 rows=3D3 width=3D4) (actual = time=3D0.120..0.120 rows=3D5 loops=3D1)" " -> Hash Join (cost=3D1.18..5.50 rows=3D3 width=3D4) = (actual time=3D0.074..0.110 rows=3D5 loops=3D1)" " Hash Cond: (a.typeid =3D t.typeid)" " -> Seq Scan on attributes a (cost=3D0.00..4.28 = rows=3D5 width=3D8) (actual time=3D0.025..0.050 rows=3D5 loops=3D1)" " Filter: ((name)::text =3D 'iconName'::text)" " -> Hash (cost=3D1.10..1.10 rows=3D6 width=3D4) = (actual time=3D0.026..0.026 rows=3D6 loops=3D1)" " -> Seq Scan on types t (cost=3D0.00..1.10 = rows=3D6 width=3D4) (actual time=3D0.004..0.010 rows=3D6 loops=3D1)" " Filter: isattributetype" "Total runtime: 231929.656 ms"