Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C6FF92E00C5 for ; Fri, 7 Dec 2007 11:36:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 54139-08 for ; Fri, 7 Dec 2007 11:36:00 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id DB14A2E00AB for ; Fri, 7 Dec 2007 11:36:06 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id lB7Fa4GS020270; Fri, 7 Dec 2007 10:36:04 -0500 (EST) To: =?ISO-8859-2?Q?Piotr_Gasid=B3o?= cc: pgsql-performance@postgresql.org Subject: Re: Trouble with LEFT JOIN using VIEWS. In-reply-to: <4759268C.6090508@barbara.eu.org> References: <4759268C.6090508@barbara.eu.org> Comments: In-reply-to =?ISO-8859-2?Q?Piotr_Gasid=B3o?= message dated "Fri, 07 Dec 2007 11:55:08 +0100" Date: Fri, 07 Dec 2007 10:36:04 -0500 Message-ID: <20269.1197041764@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/98 X-Sequence-Number: 27914 =?ISO-8859-2?Q?Piotr_Gasid=B3o?= writes: > I've just hit problem, that is unusual for me. > View definition: > SELECT users.id, users.user_name, users.extra IS NOT NULL AS has_extra > FROM users; What you've got here is a non-nullable target list, which creates an optimization fence when used in the nullable side of an outer join. The problem is that has_extra should read as NULL in the query output for a sites_secure row that has no match in users_secure_with_has_extra, but making users.extra go to null will not make that happen, so there's a constraint on where the expression can be evaluated. The current planner has no way to deal with that except by restricting the plan structure. We have some ideas about how to fix this, but don't hold your breath ... it's going to take major surgery on the planner, AFAICS. regards, tom lane