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
