Ah yes, I see the problem. I see that it is also going to be a problem where I have used CASE..WHEN in the select list of views :-( Naively, couldn't the subquery be pulled up if any non-nullable columns from the right table t2 were automatically wrapped in a simple function which returned NULL when the table row isn't matched (eg. when t2.ctid is NULL)? I'm a complete newbie to Postgres, so I have no idea if this is really possible or how hard it would be to implement in practice. Dean. >> 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq _________________________________________________________________ Feel like a local wherever you go. http://www.backofmyhand.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
