Re: Terrible plan for join to nested union

Nate Allan <nallan(at)ancestry(dot)com> writes:
> 2) Why would more effort go into Union All rather than Union?

The UNION ALL case matches up with, and shares planning and execution
code with, table-inheritance and partitioning scenarios. So yes, it
really is more interesting to more people than UNION DISTINCT.
(IIRC, the code that does that stuff was originally meant to support the
inheritance case, and we hacked UNION ALL to be able to share the logic,
not vice versa.)

Right now, UNION DISTINCT, along with INTERSECT and EXCEPT, have
basically no optimization support whatsoever: all of them go through a
code path that just evaluates both input relations and performs the
set-combination operation. All of that code dates from a period about
a dozen years ago when we were more interested in getting the right
answer at all than how fast it was. Rewriting it all to have some
optimization capability is certainly on the wish-list ... but the fact
that it hasn't risen to the top of anybody's to-do list in that time
indicates to me that it probably isn't going to get done in the next
little while either. And even if someone were to start working on it
right now, it's not a small project.

Sorry to be the bearer of bad news, but this isn't going to change
just because you try to label it a bug.

regards, tom lane