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 236E99DC86A for ; Mon, 20 Feb 2006 05:58:45 -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 53336-08 for ; Mon, 20 Feb 2006 05:58:46 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from localhost.localdomain (unknown [80.73.176.164]) by postgresql.org (Postfix) with ESMTP id D226B9DC81C for ; Mon, 20 Feb 2006 05:58:41 -0400 (AST) Received: from localhost ([127.0.0.1] helo=[192.168.1.4]) by localhost.localdomain with esmtp (Exim 4.50) id 1FB7mE-0007il-In for pgsql-performance@postgresql.org; Mon, 20 Feb 2006 10:56:18 +0100 Message-ID: <43F992D1.6040308@treyst.se> Date: Mon, 20 Feb 2006 10:58:41 +0100 From: Fredrik Olsson User-Agent: Thunderbird 1.5 (Macintosh/20051201) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: Force another plan. References: <43F74592.7010701@treyst.se> <20490.1140287811@sss.pgh.pa.us> <43F866E2.20001@treyst.se> <11673.1140370277@sss.pgh.pa.us> In-Reply-To: <11673.1140370277@sss.pgh.pa.us> 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.103 required=5 tests=[AWL=0.103] X-Spam-Score: 0.103 X-Spam-Level: X-Archive-Number: 200602/318 X-Sequence-Number: 17304 Tom Lane skrev: > Fredrik Olsson writes: > >> -> Seq Scan on t_entities (cost=0.00..1.49 rows=7 width=4) >> (actual time=404.539..409.302 rows=2 loops=1) >> 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) (actual time=27.871..27.871 rows=0 >> loops=14) >> > > This seems to be your problem right here: evaluating that subplan for > each row of t_entities is pretty expensive, and yet the planner's > estimating a total cost of only 1.49 to run the scan. What PG version > is this? AFAICT we've accounted for subplan costs in scan quals for > a long time, certainly since 7.4. Can you put together a self-contained > test case for this? > > I have found the real bottle-neck, looks like I trusted PG to do a bit too much magic. Since all table accesses go through views, and the first 11 columns always look the same, I did a "proxy-view" to simplify my "real-views" to: SELECT pv.*, ... The "proxy-view" fetches from t_entities, and so does the "real-views" to do a filter on "partof". This resulted in two scans on t_entities when only one is needed. Skipping the "proxy-view" allows PG to chose the best plan for every case. I guess I stressed the optimizer a bit too far :). Is a self contained test-case for the old way with the "proxy-view" is still wanted? -- //Fredrik Olsson Treyst AB +46-(0)19-362182 fredrik.olsson@treyst.se