Re: Bug in 8.0.0rc3 query planner: constant column in view changes execution plan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jörg Hoppe <hoppe(at)geoinformationsdienst(dot)de>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug in 8.0.0rc3 query planner: constant column in view changes execution plan
Date: 2005-01-07 21:27:08
Message-ID: 29722.1105133228@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

=?ISO-8859-15?Q?J=F6rg_Hoppe?= <hoppe(at)geoinformationsdienst(dot)de> writes:
> --- SELECTing expressions, which do not access any table data,
> --- should not influence the execution plan.

Unfortunately, that assertion is dead wrong.

> SELECT R.a_ID, R.b_ID, v.constcol
> FROM t_b R LEFT JOIN v_test_bad V on R.a_id = V.a_id
> WHERE r.b_id between 900000 and 900999
> AND v.a_id = v.a_id
> ;

The reason this behaves differently from the others is that a
constant-one column from v_test_bad won't automatically go to NULL
when the underlying table row is expanded to NULLs by the left join.
That prevents flattening of the view. See has_nullable_targetlist()
in prepjointree.c.

has_nullable_targetlist could be smarter than it is, but no improvement
in its intelligence would change the behavior in the case you give.
The only way this could be made to work is a fairly fundamental change
in the handling of variables in an execution tree, such that expressions
emitted by a view get evaluated below the point of the outer join rather
than above it. I've looked at this a bit and concluded that it probably
would not be a win overall ... indeed, it arguably might cause runtime
failures that do not occur now (eg, division by zero in a row that would
never have been evaluated otherwise).

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tony Caduto 2005-01-08 05:04:22 BUG #1375: Problem with Dollar qouting functions
Previous Message Jörg Hoppe 2005-01-07 20:42:16 Bug in 8.0.0rc3 query planner: constant column in view changes execution plan