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
– Sergey Gershkovich, Commented Apr 3, 2018 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
– Yaroslav Malyk, Commented Apr 3, 2018 at 19:20

Laurenz Albe · Accepted Answer · 2018-04-03 10:54:51Z

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 3, 2018 at 10:54

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yaroslav Malyk Over a year ago

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

Laurenz Albe Over a year ago

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

Collectives™ on Stack Overflow

Postgresql LATERAL vs INNER JOIN

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related