CODEWITHSUNDEEP
sqlpostgresqljoinlateral
Yaroslav Malyk
Yaroslav Malyk

Reputation: 459

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?

Upvotes: 5

Views: 9936

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246898

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:

  • If there are few rows in a, the LATERAL join will probably be more efficient if there is an index on b(id).

  • If there are many rows in a, the first query will probably be more efficient, because it can use a hash or merge join.

Upvotes: 7

Related Questions