Re: Postgres planner bug in 7.3.x and 7.4.1 ?

Laurent Perez <downloader(at)free(dot)fr> writes:
> We're experiencing weird behaviours with both Postgres 7.3.x and 7.4.1
> versions, relating to views based on views including fields based upon
> plpgsql functions.

There are a couple of things going on here. The principal one is a
limitation that subqueries can't be flattened into the parent query
if they are below an outer join and have non-nullable output columns
(which is presently defined as "any column that's not a simple Var").
An output column that isn't nullable wouldn't be guaranteed to go to
null in null-extended rows, thus breaking the outer join semantics.

Since the subquery doesn't get flattened, all its output columns will
still get evaluated for each row demanded from the subquery. The
optimization that discards unused output columns (including your
expensive function call) is part of the flattening process.

That applies to your problem because f1(t1.num_person) isn't nullable
--- that is, it might produce a non-null output even when num_person
is null. Had you declared f1 as strict, then in principle the system
could recognize the column as nullable. At the moment it won't, but
I'll see what I can do about fixing that for 7.5.

The other thing that's going on is a plain old bug. Given the above
restriction, the system should not have flattened the example involving
v4, but it did so because of an oversight in handling nested views.
I have fixed that here:
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/optimizer/prep/prepjointree.c
That's probably not really what you wanted to hear, since that will
guarantee the inefficient behavior in both cases :-(

The easiest workaround I can think of is to avoid putting the expensive
function call below an outer join, though that may not be real practical
for you. Alternatively, since you are evidently concerned with cases
where the function-call column won't be referenced at all, maybe you
could make a variant version of the view that doesn't contain that
column in the first place.

regards, tom lane