Re: inefficient query plan with left joined view - Mailing list pgsql-performance

Andreas Pflug <Andreas.Pflug@web.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