From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Andreas Pflug <Andreas(dot)Pflug(at)web(dot)de> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: inefficient query plan with left joined view |
Date: | 2003-01-28 18:30:07 |
Message-ID: | 22633.1043778607@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andreas Pflug | 2003-01-28 19:31:46 | Re: inefficient query plan with left joined view |
Previous Message | Andreas Pflug | 2003-01-28 16:50:58 | inefficient query plan with left joined view |