BUG #14642: Excessive sorting node appears in the plan

The following bug has been logged on the website:

Bug reference: 14642
Logged by: Alexey Bashtanov
Email address: bashtanov(at)imap(dot)cc
PostgreSQL version: 9.6.2
Operating system: Ubuntu Linux 14.04
Description:

Hello,

Under certain circumstances, an excessive sorting node appears in the
plan.
It happens when at the same time:
1) There is a merge join node with a join by two equality clauses (say,
`innr.p=outr.p AND innr.q=outr.q`);
2) One of the children, say the inner one, comes as a `SELECT p, q FROM ..
WHERE p = 0 GROUP BY p, q` or `GROUP BY q`, doesn't matter.
3) It uses sort+groupAgg, not hashAgg.

Then the result of the grouping, being already sorted by p (which is always
1), and q, gets sorted again for the merge join.

It looks like the grouping subplan is aware of p=1 and simplifies its
pathkeys from [(innr.p, 1), (innr.q)] to [(innr.q)]. However, looks like it
does not share this kind of knowledge with the code that plans the merge
join, so the merge join keeps requiring the data coming in [innr.p, innr.q]
order.

Do blackbox-style subplans, such as subqueries with grouping, share only the
resulting stream order info with the enclosing query, not the equivalence
classes?

This is the example:
---
set enable_hashjoin to off;
set enable_hashagg to off;
set enable_nestloop to off;

explain
select *
from (
select outr%2 o2,
outr%5 o5
from generate_series(1, 10) outr
order by o2,
o5
) o
join (
select innr%2 i2,
innr%5 i5
from generate_series(1, 10) innr
where innr%2 = 0
group by innr%2,
innr%5
) i on o.o2 = i.i2
and o.o5 = i.i5;
---

I tried 9.4.11, 9.6.2, and master versions.

Best regards,
Alexey Bashtanov