Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 37E2F47728F for ; Tue, 28 Jan 2003 14:41:54 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 5054D4775DC for ; Tue, 28 Jan 2003 13:30:09 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h0SIU85u022634; Tue, 28 Jan 2003 13:30:08 -0500 (EST) To: Andreas Pflug Cc: pgsql-performance@postgresql.org Subject: Re: inefficient query plan with left joined view In-reply-to: <3E36B4F2.1020506@web.de> References: <3E36B4F2.1020506@web.de> Comments: In-reply-to Andreas Pflug message dated "Tue, 28 Jan 2003 17:50:58 +0100" Date: Tue, 28 Jan 2003 13:30:07 -0500 Message-ID: <22633.1043778607@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/372 X-Sequence-Number: 1019 Andreas Pflug writes: > What I'm doing on V7.3.1: > select t1.keycol, t2.keycol > from tab1 t1 > LEFT join myview t2 on t1.keycol=t2.keycol > where t1.keycol=1000001 > the view is created as > CREATE myview AS SELECT keycol, 22::integer as calc_col FROM tab2 The subquery isn't pulled up because it doesn't pass the has_nullable_targetlist test in src/backend/optimizer/plan/planner.c. If we did flatten it, then references to calc_col wouldn't correctly go to NULL when the LEFT JOIN should make them do so --- they'd be 22 all the time. As the notes in that routine say, it could be made smarter: strict functions of nullable variables could be allowed. So if your real concern is not '22' but something like 'othercol + 22' then this is fixable. regards, tom lane