Postgresql LATERAL vs INNER JOIN

Question

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?

Creek

The several or one rows is not efficient to LATERAL. LATERAL is more efficient when b contains many rows that are not associated with the selected rows from a. – Surpassing

stop, but not the point, in order to be united? you say that they will be more effective if they are not bound - then the meaning of the request is lost – Creek

Answer 1

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.

Royroyal

Hi - I tested - if I have 10 records of the upper level and for each level I have 200 000 entries-because of INNER I got 2.5 seconds to do this, After that I made 200,000 records of the first and second levelLATERAL cope for 3 minute – Creek

Sure, makes sense. As I said, it depends on your data. – Royroyal