Re: inefficient query plan with left joined view

Andreas Pflug <Andreas(dot)Pflug(at)web(dot)de> 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