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?

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. — Sergey Gershkovich, Apr 03 '18 at 10:45
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 — Yaroslav Malyk, Apr 03 '18 at 19:20

score 7 · Accepted Answer · answered Apr 03 '18 at 10:54

7

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.

answered Apr 03 '18 at 10:54

Laurenz Albe

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 – Yaroslav Malyk Apr 05 '18 at 17:01
Sure, makes sense. As I said, it depends on your data. – Laurenz Albe Apr 05 '18 at 17:11

1 Answers1