Re: BUG #18751: Sub-optimal UNION ALL plan

Kirill Reshke <reshkekirill@gmail.com> writes:
> I reproduced this on REL_16_STABLE, HEAD & REL_13_STABLE, so this is
> not really a bug, just a missing optimization?

Yeah.  I believe what is happening is that the addition of the WHERE
clause forces the second sub-SELECT to be planned as an independent
query.  And that level of planning has no idea that it might be
useful to produce a result ordered by "t", so it doesn't generate
a sub-plan that can do that.  Then the best that the outer level
can do is sort after-the-fact.

You could work around this by writing the second sub-SELECT like

    (select * from t2 where ... order by t)

            regards, tom lane