X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D3D3A9DC84A for ; Sat, 18 Feb 2006 12:04:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 85819-06 for ; Sat, 18 Feb 2006 12:04:42 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 529799DC813 for ; Sat, 18 Feb 2006 12:04:41 -0400 (AST) Received: from localhost.localdomain (unknown [80.73.176.164]) by svr4.postgresql.org (Postfix) with ESMTP id CC3905AF0A6 for ; Sat, 18 Feb 2006 16:04:40 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=[192.168.1.4]) by localhost.localdomain with esmtp (Exim 4.50) id 1FAUXG-0005Mu-6c for pgsql-performance@postgresql.org; Sat, 18 Feb 2006 17:02:14 +0100 Message-ID: <43F74592.7010701@treyst.se> Date: Sat, 18 Feb 2006 17:04:34 +0100 From: Fredrik Olsson User-Agent: Thunderbird 1.5 (Macintosh/20051201) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Force another plan. Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.08 required=5 tests=[AWL=0.080] X-Spam-Score: 0.08 X-Spam-Level: X-Archive-Number: 200602/307 X-Sequence-Number: 17293 I have some quite huge queries, inside functions, so debugging is kind of hard. But I have located the query that for some reason gets 4 times as slow after an analyze. Before analyze the plan for the query is this: Nested Loop (cost=16.80..34.33 rows=1 width=28) Join Filter: (ischildof(2, "outer".calendar) OR (hashed subplan)) -> Nested Loop (cost=0.00..11.66 rows=1 width=32) -> Index Scan using t_events_eventype on t_events e (cost=0.00..5.82 rows=1 width=28) Index Cond: (eventtype = 1) Filter: (rrfreq IS NOT NULL) -> Index Scan using t_entities_pkey on t_entities te (cost=0.00..5.83 rows=1 width=4) Index Cond: (te."ID" = "outer".entity) Filter: (partof = 'events'::name) -> Index Scan using t_entities_pkey on t_entities (cost=0.00..5.85 rows=1 width=4) Index Cond: (t_entities."ID" = "outer".entity) Filter: ((haveaccess(createdby, responsible, "class", false) OR CASE WHEN (partof = 'contacts'::name) THEN ischildof(ancestorof(me()), "ID") ELSE false END) AND (subplan)) SubPlan -> Function Scan on alleventoccurances (cost=0.00..12.50 rows=1000 width=8) SubPlan -> Seq Scan on t_attendees (cost=0.00..16.38 rows=170 width=4) Filter: ischildof(2, contact) In reality this takes approximately 1.0s in the general case. After an analyze the plan becomes: Nested Loop (cost=2.09..4.82 rows=1 width=28) Join Filter: ("inner"."ID" = "outer"."ID") -> Hash Join (cost=2.09..3.59 rows=1 width=32) Hash Cond: ("outer"."ID" = "inner".entity) Join Filter: (ischildof(2, "inner".calendar) OR (hashed subplan)) -> Seq Scan on t_entities (cost=0.00..1.46 rows=6 width=4) Filter: ((haveaccess(createdby, responsible, "class", false) OR CASE WHEN (partof = 'contacts'::name) THEN ischildof(ancestorof(me()), "ID") ELSE false END) AND (subplan)) SubPlan -> Function Scan on alleventoccurances (cost=0.00..12.50 rows=1000 width=8) -> Hash (cost=1.06..1.06 rows=2 width=28) -> Seq Scan on t_events e (cost=0.00..1.06 rows=2 width=28) Filter: ((rrfreq IS NOT NULL) AND (eventtype = 1)) SubPlan -> Seq Scan on t_attendees (cost=0.00..1.02 rows=1 width=4) Filter: ischildof(2, contact) -> Seq Scan on t_entities te (cost=0.00..1.16 rows=5 width=4) Filter: (partof = 'events'::name) This takes on approximately 4.5s. So obviously it has degraded. I count myself as a newbie here, so any hints on what goes on, why a plan might be chosen, and how I can make is better is appreciated. Naturally the I can provide scripts to set up all or parts of the database if anyone like. regards -- //Fredrik Olsson Treyst AB +46-(0)19-362182 fredrik.olsson@treyst.se