Ich habe mir nun mal die Mühe gemacht, den Explain mi den SQL's zu prüfen:

left join Lateral mit where order by limit
führt zu
Index Probe => Table Probe => Fetch n Rows => nested Loop Join

left join CTE mit On-Klausel führt zu
Index Probe => Table Probe => Aggregation => nested Loop

Der Unterschied ist, dass nach dem Lateral nur noch ein Fetch einer Zeile passiert, während bei dem Grouped-Join noch ein Aggregat gebildet werden muss, was durchaus mehr als 1 Zeile umfassen kann.

Gegen den CTE spricht ebenso, dass ich einen 2. Left join mit dem CTE benötige, wenn ich weitere Werte aus dem Satz haben muss.
Das funktioniert halt nicht mit dem CTE, da ich hier einen Group By benötige um Aggregate verwenden zu können.
Dies führt dann zu einem 2. Index Probe => Table Probe => nested Loop

Wie du an meinem Lateral siehst, benötige ich nur ein b.* um aus dem Lateral alles zu erhalten, was ich benötige.

Ein weiteres Argument gegen CTE's ist leider auch, dass ich diese nicht in einem Insert oder Merge verwenden kann, sondern hier auf derived Table gehen muss.

Es gibt aber noch eine weitere Anwendung für lateral.
Wer kennt nicht die Tabellen mit pseudo Array-Feldern, die durchnummeriert sind.
Bisher musste man diese mit einem etwas kompexeren union all trennen um sie per SQL vernünftig zu verarbeiten:

Code:
select artikel, menge01 as menge from myfile
union all
select artikel, menge02 as menge from myfile
:
Dies führt dann zu n * Indexscan => Table Probe => union all

Mit Lateral ist das nun erheblich einfacher:

Code:
select artikel, x.* from myfile
cross join lateral (
  values(menge01)
   union all 
  values(menge02)
) x (menge)
Dies führt zu n * values list => union all => nested loop

Außerdem gehört Lateral bei vielen anderen DBM's inzwischen zum Standard.

Wer mehr wissen will:
https://medium.com/@goldengrisha/a-d...n-7b09fcb3b745