BUG #15820: Commuting two column make a SubqueryScan node to appear in the plan

The following bug has been logged on the website:

Bug reference: 15820
Logged by: Luis M Carril
Email address: luis(dot)carril(at)swarm64(dot)com
PostgreSQL version: 11.3
Operating system: Alpine 3.9
Description:

Hi,
switching the order of two columns make a SubqueryScan node to appear in
the plan. It seems completely trivial as the cost is not modified, should it
also be removed in the second case?

Setup in the postgresl 11.3 alpine docker container:

CREATE TABLE students (id INT, stud_name TEXT, major TEXT, grad_year DATE)
;
CREATE TABLE exams (sid INT, course TEXT, curriculum TEXT, taken_at DATE,
grade INT) ;

INSERT INTO students VALUES (1, 'John Doe', 'Mathematic', 'Jan 01
2017'::DATE), (2, 'John Roe', 'Physics', 'Jan 01 2017'::DATE), (3, 'John
Moe', 'Physics', 'Jan 01 2019'::DATE), (4, 'John Soe', 'Mathematic', 'Jan 01
2019'::DATE), (5, 'John no Exams', 'Mathematic', 'Jan 01 2023'::DATE);

INSERT INTO exams VALUES (1, 'Basic Math', 'Mathematic', 'Jan 10
2016'::DATE, 1), (1, 'Advanced Math', 'Mathematic', 'May 3 2016'::DATE, 3),
(1, 'Statistics', 'Mathematic', 'Sep 5 2016'::DATE, 4), (4, 'Basic Math',
'Mathematic', 'Sep 5 2018'::DATE, 1), (4, 'Advanced Math', 'Mathematic',
'Oct 5 2018'::DATE, 1), (4, 'Statistics', 'Mathematic', 'Oct 5 2018'::DATE,
1), (2, 'Basic Physics', 'Physics', 'Mar 5 2016'::DATE, 1), (2,
'Relativity', 'Physics', 'Apr 5 2016'::DATE, 1), (2, 'Quantum mechanics',
'Physics', 'Dec 5 2016'::DATE, 1), (3, 'Basic Physics', 'Physics', 'Mar 5
2018'::DATE, 2), (3, 'Relativity', 'Physics', 'Apr 5 2018'::DATE, 1), (3,
'Quantum mechanics', 'Physics', 'Dec 5 2018'::DATE, 3);

SET enable_nestloop to FALSE;

Now we execute the same query and only change the order of the columns in
the subquery. We can see that for the second case a 'Subquery Scan on m'
appears, although it barely modifies the total cost.

postgres=# EXPLAIN SELECT s.stud_name,e.course, e.grade FROM students
s,exams e, (SELECT e2.sid as id, MIN(e2.grade) AS best FROM exams e2 GROUP
BY e2.sid) m WHERE s.id=e.sid AND m.id=s.id and best = e.grade;
QUERY PLAN

-----------------------------------------------------------------------------------------
Hash Join (cost=50.64..71.94 rows=16 width=68)
Hash Cond: (s.id = e.sid)
-> Seq Scan on students s (cost=0.00..18.10 rows=810 width=36)
-> Hash (cost=50.59..50.59 rows=4 width=44)
-> Hash Join (cost=28.70..50.59 rows=4 width=44)
Hash Cond: ((e.sid = e2.sid) AND (e.grade =
(min(e2.grade))))
-> Seq Scan on exams e (cost=0.00..17.80 rows=780
width=40)
-> Hash (cost=25.70..25.70 rows=200 width=8)
-> HashAggregate (cost=21.70..23.70 rows=200
width=8)
Group Key: e2.sid
-> Seq Scan on exams e2 (cost=0.00..17.80
rows=780 width=8)
(11 rows)

postgres=# EXPLAIN SELECT s.stud_name,e.course, e.grade FROM students
s,exams e, (SELECT MIN(e2.grade) AS best, e2.sid as id FROM exams e2 GROUP
BY e2.sid) m WHERE s.id=e.sid AND m.id=s.id and best = e.grade;
QUERY PLAN

-----------------------------------------------------------------------------------------------
Hash Join (cost=50.64..71.94 rows=16 width=68)
Hash Cond: (s.id = e.sid)
-> Seq Scan on students s (cost=0.00..18.10 rows=810 width=36)
-> Hash (cost=50.59..50.59 rows=4 width=44)
-> Hash Join (cost=28.70..50.59 rows=4 width=44)
Hash Cond: ((e.sid = m.id) AND (e.grade = m.best))
-> Seq Scan on exams e (cost=0.00..17.80 rows=780
width=40)
-> Hash (cost=25.70..25.70 rows=200 width=8)
-> Subquery Scan on m (cost=21.70..25.70 rows=200
width=8)
-> HashAggregate (cost=21.70..23.70 rows=200
width=8)
Group Key: e2.sid
-> Seq Scan on exams e2 (cost=0.00..17.80
rows=780 width=8)

Version:
select version();
version

---------------------------------------------------------------------------------------
PostgreSQL 11.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 8.3.0)
8.3.0, 64-bit
(1 row)