Failure to reordering in case of a lateral join in combination with a left join (not inner join) resulting in suboptimal nested loop plan

Hi,

The queries in what follows can be executed on the following fiddle:
*https://dbfiddle.uk/?rdbms=postgres_10&fiddle=64542f2d987d3ce0d85bbc40ddadf7d6
<https://dbfiddle.uk/?rdbms=postgres_10&fiddle=64542f2d987d3ce0d85bbc40ddadf7d6>*
- Please
note that the queries/functions might look silly/pointless, I extracted the
performance issue I am seeing to a minimal reproducible example.

I have the following schema:

create table parent
(
id int primary key
);
create table child
(
id int primary key,
parent_id int references parent(id)
);
create index on child(parent_id);

Let's start with the following inlinable setof-returning function which
basically returns the child rows for a given parent identifier:

create function child_get_1(int) returns table(a int) as
$$
select child.id
from child
left join parent
on parent.id = child.parent_id
where child.parent_id = $1;
$$
language sql stable;

Note that the left join branch is intentionally not used, and thus could be
eliminated by the planner.

When executing the following query, I get a satisfying hash (left) join
(and the left join to parent is indeed eliminated):

explain analyze
select ch.* from parent p, child_get_1(p.id) ch;

+--------------------------------------------------------------------------------------------------------------------+
| Hash Join (cost=3.25..17163.23 rows=999900 width=4) (actual
time=0.025..194.279 rows=999900 loops=1) |
| Hash Cond: (child.parent_id = p.id)
|
| -> Seq Scan on child (cost=0.00..14424.00 rows=999900 width=8)
(actual time=0.005..47.215 rows=999900 loops=1) |
| -> Hash (cost=2.00..2.00 rows=100 width=4) (actual time=0.016..0.016
rows=100 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 12kB
|
| -> Seq Scan on parent p (cost=0.00..2.00 rows=100 width=4)
(actual time=0.001..0.007 rows=100 loops=1) |
+--------------------------------------------------------------------------------------------------------------------+

Now, I introduce a convenience function, also inlinable, which fetches the
child rows by its parent id:

create function t(int) returns setof child as
$$
select child.* from child where child.parent_id = $1;
$$
language sql stable;

I refactor `child_get_1(int)` from above as following:

create function child_get_2(int) returns table(a int) as
$$
select child.id
from t($1) child
left join parent
on parent.id = child.parent_id;
$$
language sql stable;

explain analyze
select ch.* from parent p, child_get_2(p.id) ch;

Now, I get a nested loop, which as expected performs quite badly:

+--------------------------------------------------------------------------------------------------------------------------------------------+
| Nested Loop (cost=189.92..493990.48 rows=999900 width=4) (actual
time=1.519..713.680 rows=999900 loops=1) |
| -> Seq Scan on parent p (cost=0.00..2.00 rows=100 width=4) (actual
time=0.004..0.081 rows=100 loops=1) |
| -> Bitmap Heap Scan on child (cost=189.92..4739.90 rows=9999 width=4)
(actual time=1.365..6.332 rows=9999 loops=100) |
| Recheck Cond: (parent_id = p.id)
|
| Heap Blocks: exact=442476
|
| -> Bitmap Index Scan on child_parent_id_idx (cost=0.00..187.42
rows=9999 width=0) (actual time=0.838..0.838 rows=9999 loops=100) |
| Index Cond: (parent_id = p.id)
|
+--------------------------------------------------------------------------------------------------------------------------------------------+

For some reason I cannot explain we now end up with a nested loop, instead
an hash join. The fairly trivial introduction of `t(int)` messes up with
reordering, but I fail to see why. I manually upped the from and join
collapse limit to 32 - just to be sure -, but no effect. Also, the left
join branch could not be eliminated. I believe this is related to the usage
of the implicit lateral join to `child_get_2(p.id)` in the main query,
which somehow messes up with the reordering of `from t($1) as child` in
`child_get_2(int)`, though I am not 100% sure.

Also, note that when we apply an inner join instead of a left join, the
problem goes away. The planner now manages to end up with a hash join in
both cases.

I am seeing this on v10 and v11.

Any ideas?

Thank you. Best regards.