Why does this LEFT JOIN perform so much worse than LEFT JOIN LATERAL?

I have the following tables (taken from the Sakila database):

• film: film_id is pkey

• actor: actor_id is pkey

• film_actor: film_id and actor_id are fkeys to film/actor

I am selecting a particular film. For this film, I also want all actors participating in that film. I have two queries for this: one with a LEFT JOIN and one with a LEFT JOIN LATERAL.

select film.film_id, film.title, a.actors
from   film
left join
  (         
       select     film_actor.film_id, array_agg(first_name) as actors
       from       actor
       inner join film_actor using(actor_id)
       group by   film_actor.film_id
  ) as a
on       a.film_id = film.film_id
where    film.title = 'ACADEMY DINOSAUR'
order by film.title;

select film.film_id, film.title, a.actors
from   film
left join lateral
  (
       select     array_agg(first_name) as actors
       from       actor
       inner join film_actor using(actor_id)
       where      film_actor.film_id = film.film_id
  ) as a
on       true
where    film.title = 'ACADEMY DINOSAUR'
order by film.title;

When comparing the query plan, the first query performs much worse (20x) than the second:

```
Merge Left Join (cost=507.20..573.11 rows=1 width=51) (actual time=15.087..15.089 rows=1 loops=1)
Merge Cond: (film.film_id = film_actor.film_id)
-> Sort (cost=8.30..8.31 rows=1 width=19) (actual time=0.075..0.075 rows=1 loops=1)
Sort Key: film.film_id
Sort Method: quicksort Memory: 25kB
-> Index Scan using idx_title on film (cost=0.28..8.29 rows=1 width=19) (actual time=0.044..0.058 rows=1 loops=1)
Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text)
-> GroupAggregate (cost=498.90..552.33 rows=997 width=34) (actual time=15.004..15.004 rows=1 loops=1)
Group Key: film_actor.film_id
-> Sort (cost=498.90..512.55 rows=5462 width=8) (actual time=14.934..14.937 rows=11 loops=1)
Sort Key: film_actor.film_id
Sort Method: quicksort Memory: 449kB

Test setup

Your original setup in the fiddle leaves room for improvement. I kept asking for your setup for a reason.

You have these indexes on film_actor:

"film_actor_pkey" PRIMARY KEY, btree (actor_id, film_id)  
"idx_fk_film_id" btree (film_id)

Which is pretty helpful already. But to best support your particular query, you would have a multicolumn index on (film_id, actor_id), columns in this order. A practical solution: replace idx_fk_film_id with an index on (film_id, actor_id) - or create the PK on (film_id, actor_id) for the purpose of this test, like I do below. See:

• Is a composite index also good for queries on the first field?

In a read-only (or mostly, or generally when VACUUM can keep up with write activity) it also helps to have an index on (title, film_id) to allow index only-scans. My test case is now highly optimized for read performance.

Type mismatch between film.film_id (integer) and film_actor.film_id (smallint). While that works it makes queries slower and can lead to various complications. Also makes FK constraints more expensive. Never do this if it can be avoided. If you are not sure, pick integer over smallint. While smallint can save 2 bytes per field (often consumed by alignment padding) there are more complication than with integer.

To optimize the performance of the test itself, create indexes and constraints after bulk-inserting lots of rows. It is substantially slower to add tuples incrementally to existing indexes than to create them from scratch with all rows present.

Unrelated to this test:

-
Free-standing sequences plus column defaults instead of much simpler and more reliable serial (or IDENTITY) columns. Don't.

• Auto increment table column

-
timestamp without timestamp is typically unreliable for a column like last_update. Use timestamptz instead. And note that column defaults do not cover the "last update", strictly speaking.

-
The length modifier in character varying(255) indicates that the test case is not intended for Postgres to begin with because the odd length is pretty pointless here. (Or the author is clueless.)

Consider the audited test case building on your fiddle, optimized, and with added queries.

fiddle

Related:

• How to implement a many-to-many relationship in PostgreSQL?

A test setup with a 1000 films and 200 actors has limited validity. The most efficient queries take

-
For selecting few rows (your test!), different query techniques yield better results. That's where LATERAL comes in. It carries more overhead but only reads required rows from sub-tables. A big win if only a (very) small fraction is needed.

For your particular test case, consider an ARRAY constructor in the LATERAL subquery:

SELECT f.film_id, f.title, a.actors
FROM   film
LEFT   JOIN LATERAL (
   SELECT ARRAY (
      SELECT a.first_name
      FROM   film_actor fa
      JOIN   actor a USING (actor_id)
      WHERE  fa.film_id = f.film_id
      ) AS actors
   ) a ON true
WHERE  f.title = 'ACADEMY DINOSAUR';
-- ORDER  BY f.title;  -- redundant while we filter for a single title

While only aggregating a single array in the lateral subquery, a simple ARRAY constructor performs better than the aggregate function array_agg(). See:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?

Or with a lowly correlated subquery for the simple case:

SELECT f.film_id, f.title
     , ARRAY (SELECT a.first_name
              FROM   film_actor fa
              JOIN   actor a USING (actor_id)
              WHERE  fa.film_id = f.film_id) AS actors
FROM   film f
WHERE  f.title = 'ACADEMY DINOSAUR';

Or, very basically, just 2x LEFT JOIN and then aggregate:

SELECT f.film_id, f.title, array_agg(a.first_name) AS actors
FROM   film f
LEFT   JOIN film_actor fa USING (film_id)
LEFT   JOIN actor a USING (actor_id)
WHERE  f.title = 'ACADEMY DINOSAUR'
GROUP  BY f.film_id;

These three seem fastest in my updated fiddle (planning + execution time).

Your first attempt (only slightly modified) is typically fastest to retrieve all or most films, but not for a small selection:

SELECT f.film_id, f.title, a.actors
FROM   film f
LEFT   JOIN (         
   SELECT fa.film_id, array_agg(first_name) AS actors
   FROM   actor
   JOIN   film_actor fa USING (actor_id)
   GROUP  by fa.film_id
   ) a USING (film_id)
WHERE  f.title = 'ACADEMY DINOSAUR';  -- not good for a single (or few) films!

Tests with much bigger cardinalities will be more revealing. And don't generalize results lightly, there are many factors for total performance.

StackExchange Database Administrators Q#207368, answer score: 11