PG Bug reporting form <noreply@postgresql.org> writes:
> The join condition is not pushed down to a subquery containing UNION ALL
> when even a single query within the subquery contains a WHERE clause.
I think this is less about "can't push down" than "can't pull up",
specifically that prepjointree.c fails to flatten that subquery
into an "appendrel member", per the comments in is_safe_append_member:
* It's only safe to pull up the child if its jointree contains exactly
* one RTE, else the AppendRelInfo data structure breaks. The one base RTE
* could be buried in several levels of FromExpr, however. Also, if the
* child's jointree is completely empty, we can pull up because
* pull_up_simple_subquery will insert a single RTE_RESULT RTE instead.
*
* Also, the child can't have any WHERE quals because there's no place to
* put them in an appendrel. (This is a bit annoying...)
I don't recall at the moment if there are fundamental reasons not to
have per-child quals in appendrels, or if it could be done with the
application of enough elbow grease. But it's probably not trivial.
That comment has been there quite awhile.
regards, tom lane