Re: Performance problem (outer join + view + non-strict functions)‏

Dean Rasheed <dean_rasheed(at)hotmail(dot)com> writes:
> I am having performance problems running a number of queries
> involving views based on non-strict functions. I have reproduced the
> problem with the simple test-case below which shows how the query plan
> is different depending on whether the view uses strict or non-strict
> functions (even though those columns do not appear in the WHERE
> clause).

Subqueries that produce non-nullable output columns can't be pulled up
underneath the nullable side of an outer join, because their output
values wouldn't go to NULL properly when expanding an unmatched row
from the other side of the join (see has_nullable_targetlist in
prepjointree.c). In this context that means that we can't recognize
the option of using a inner indexscan for the table within the subquery.

I have some vague ideas about how to eliminate that restriction,
but don't hold your breath. At the earliest it might happen in 8.4.

regards, tom lane