Regression: Cross Join Lateral with two streams chooses a non-optimal join order.

[sim1984]

We have the following query:

SELECT
  COUNT(*)
FROM
  HORSE
  CROSS JOIN LATERAL (
    SELECT R.CODE_FARM
    FROM REGISTRATION R
    WHERE R.CODE_REGTYPE = 6
      AND R.CODE_HORSE = HORSE.CODE_HORSE
    ORDER BY R.BYDATE DESC
    FETCH FIRST ROW ONLY
  ) T
  JOIN FARM ON FARM.CODE_FARM = T.CODE_FARM
WHERE HORSE.CODE_HORSE = 742363

Select Expression
    -> Aggregate
        -> Nested Loop Join (inner)
            -> Filter
                -> Table "HORSE" Access By ID
                    -> Bitmap
                        -> Index "PK_HORSE" Unique Scan
            -> Table "FARM" Full Scan
            -> Filter
                -> First N Records
                    -> Filter
                        -> Filter
                            -> Table "REGISTRATION" as "T R" Access By ID
                                -> Index "IDX_REGISTRATION_OWNER" Range Scan (partial match: 2/3)

                COUNT
=====================
                    1

Current memory = 551764736
Delta memory = 464
Max memory = 551834048
Elapsed time = 0.194 sec
Buffers = 32768
Reads = 0
Writes = 0
Fetches = 244503

If you do the same in 4.0, the plan is more correct and the speed is higher:

Select Expression
    -> Aggregate
        -> Nested Loop Join (inner)
            -> Filter
                -> Table "HORSE" Access By ID
                    -> Bitmap
                        -> Index "PK_HORSE" Unique Scan
            -> Nested Loop Join (inner)
                -> First N Records
                    -> Filter
                        -> Filter
                            -> Table "REGISTRATION" as "T R" Access By ID
                                -> Index "IDX_REGISTRATION_OWNER" Range Scan (partial match: 2/3)
                -> Filter
                    -> Table "FARM" Access By ID
                        -> Bitmap
                            -> Index "PK_FARM" Unique Scan

                COUNT
=====================
                    1

Current memory = 280424528
Delta memory = -112
Max memory = 280499152
Elapsed time = 0.013 sec
Buffers = 16384
Reads = 0
Writes = 0
Fetches = 99