sqlpostgresqljoinlateral

Postgresql LATERAL vs INNER JOIN

JOIN

SELECT *
FROM a
  INNER JOIN (
    SELECT b.id, Count(*) AS Count
    FROM b
    GROUP BY b.id ) AS b ON b.id = a.id;

LATERAL

SELECT *
FROM a,
  LATERAL (
    SELECT Count(*) AS Count
    FROM b
    WHERE a.id = b.id ) AS b;

I understand that here join will be computed once and then merge with the main request vs the request for each FROM.

It seems to me that if join will rotate a few rows to one frame then it will be more efficient but if it will be 1 to 1 then LATERAL - I think right?

Solution

  • If I understand you right you are asking which of the two statements is more efficient.

    You can test that yourself using EXPLAIN (ANALYZE), and I guess that the answer depends on the data: