Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE SEQUENCE actor_actor_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE TABLE actor (
    actor_id integer DEFAULT nextval('actor_actor_id_seq'::regclass) NOT NULL,
    first_name character varying(45) NOT NULL,
    last_name character varying(45) NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);

CREATE SEQUENCE film_film_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

CREATE TABLE film (
    film_id integer DEFAULT nextval('film_film_id_seq'::regclass) NOT NULL,
    title character varying(255) NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);

CREATE TABLE film_actor (
    actor_id smallint NOT NULL,
    film_id smallint NOT NULL,
    last_update timestamp without time zone DEFAULT now() NOT NULL
);

ALTER TABLE ONLY actor
    ADD CONSTRAINT actor_pkey PRIMARY KEY (actor_id);
ALTER TABLE ONLY film
    ADD CONSTRAINT film_pkey PRIMARY KEY (film_id);
ALTER TABLE ONLY film_actor

CREATE SEQUENCE

CREATE TABLE

CREATE SEQUENCE

CREATE TABLE

CREATE TABLE

ALTER TABLE

ALTER TABLE

ALTER TABLE

ALTER TABLE

ALTER TABLE

CREATE INDEX

CREATE INDEX

INSERT 0 200

INSERT 0 1000

INSERT 0 5462

vacuum analyze;

VACUUM

explain analyze
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;

QUERY PLAN

Merge Left Join  (cost=453.12..519.03 rows=1 width=51) (actual time=2.994..2.995 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.196..0.196 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.013..0.016 rows=1 loops=1)

              Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text)

  ->  GroupAggregate  (cost=444.82..498.25 rows=997 width=34) (actual time=2.791..2.792 rows=1 loops=1)

        Group Key: film_actor.film_id

        ->  Sort  (cost=444.82..458.47 rows=5462 width=8) (actual time=2.771..2.772 rows=11 loops=1)

              Sort Key: film_actor.film_id

              Sort Method: quicksort  Memory: 449kB

              ->  Hash Join  (cost=6.50..105.76 rows=5462 width=8) (actual time=0.093..1.698 rows=5462 loops=1)

                    Hash Cond: (film_actor.actor_id = actor.actor_id)

                    ->  Seq Scan on film_actor  (cost=0.00..84.62 rows=5462 width=4) (actual time=0.008..0.405 rows=5462 loops=1)

                    ->  Hash  (cost=4.00..4.00 rows=200 width=10) (actual time=0.064..0.064 rows=200 loops=1)

                          Buckets: 1024  Batches: 1  Memory Usage: 17kB

                          ->  Seq Scan on actor  (cost=0.00..4.00 rows=200 width=10) (actual time=0.006..0.031 rows=200 loops=1)

Planning time: 2.546 ms

Execution time: 3.315 ms

EXPLAIN

explain analyze
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;

QUERY PLAN

Nested Loop Left Join  (cost=23.15..31.20 rows=1 width=51) (actual time=0.544..0.546 rows=1 loops=1)

  ->  Index Scan using idx_title on film  (cost=0.28..8.29 rows=1 width=19) (actual time=0.012..0.013 rows=1 loops=1)

        Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text)

  ->  Aggregate  (cost=22.87..22.88 rows=1 width=32) (actual time=0.530..0.530 rows=1 loops=1)

        ->  Hash Join  (cost=18.32..22.86 rows=5 width=6) (actual time=0.467..0.518 rows=10 loops=1)

              Hash Cond: (actor.actor_id = film_actor.actor_id)

              ->  Seq Scan on actor  (cost=0.00..4.00 rows=200 width=10) (actual time=0.007..0.023 rows=200 loops=1)

              ->  Hash  (cost=18.26..18.26 rows=5 width=2) (actual time=0.415..0.415 rows=10 loops=1)

                    Buckets: 1024  Batches: 1  Memory Usage: 9kB

                    ->  Bitmap Heap Scan on film_actor  (cost=4.32..18.26 rows=5 width=2) (actual time=0.380..0.401 rows=10 loops=1)

                          Recheck Cond: (film_id = film.film_id)

                          Heap Blocks: exact=10

                          ->  Bitmap Index Scan on idx_fk_film_id  (cost=0.00..4.32 rows=5 width=0) (actual time=0.320..0.320 rows=10 loops=1)

                                Index Cond: (film_id = film.film_id)

Planning time: 0.739 ms

Execution time: 0.631 ms

EXPLAIN

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

QUERY PLAN

GroupAggregate  (cost=29.44..29.49 rows=1 width=51) (actual time=0.158..0.160 rows=1 loops=1)

  Group Key: film.film_id

  ->  Sort  (cost=29.44..29.45 rows=5 width=25) (actual time=0.144..0.147 rows=10 loops=1)

        Sort Key: film.film_id

        Sort Method: quicksort  Memory: 25kB

        ->  Nested Loop Left Join  (cost=4.74..29.38 rows=5 width=25) (actual time=0.074..0.133 rows=10 loops=1)

              ->  Index Scan using idx_title on film  (cost=0.28..8.29 rows=1 width=19) (actual time=0.033..0.034 rows=1 loops=1)

                    Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text)

              ->  Nested Loop  (cost=4.47..19.09 rows=200 width=8) (actual time=0.039..0.094 rows=10 loops=1)

                    ->  Bitmap Heap Scan on film_actor  (cost=4.32..18.26 rows=5 width=4) (actual time=0.028..0.042 rows=10 loops=1)

                          Recheck Cond: (film_id = film.film_id)

                          Heap Blocks: exact=10

                          ->  Bitmap Index Scan on idx_fk_film_id  (cost=0.00..4.32 rows=5 width=0) (actual time=0.022..0.022 rows=10 loops=1)

                                Index Cond: (film_id = film.film_id)

                    ->  Index Scan using actor_pkey on actor  (cost=0.14..0.17 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=10)

                          Index Cond: (actor_id = film_actor.actor_id)

Planning time: 0.739 ms

Execution time: 0.247 ms

EXPLAIN

explain analyze
select film.film_id, film.title, array_agg(a.first_name) as actors
from   film
left join lateral
  (
       select     actor.first_name
       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'
group by film.film_id, film.title
order by film.title;

QUERY PLAN

GroupAggregate  (cost=29.44..29.49 rows=1 width=51) (actual time=0.076..0.077 rows=1 loops=1)

  Group Key: film.film_id

  ->  Sort  (cost=29.44..29.45 rows=5 width=25) (actual time=0.070..0.071 rows=10 loops=1)

        Sort Key: film.film_id

        Sort Method: quicksort  Memory: 25kB

        ->  Nested Loop Left Join  (cost=4.74..29.38 rows=5 width=25) (actual time=0.036..0.064 rows=10 loops=1)

              ->  Index Scan using idx_title on film  (cost=0.28..8.29 rows=1 width=19) (actual time=0.008..0.009 rows=1 loops=1)

                    Index Cond: ((title)::text = 'ACADEMY DINOSAUR'::text)

              ->  Nested Loop  (cost=4.47..19.09 rows=200 width=8) (actual time=0.027..0.052 rows=10 loops=1)

                    ->  Bitmap Heap Scan on film_actor  (cost=4.32..18.26 rows=5 width=4) (actual time=0.019..0.026 rows=10 loops=1)

                          Recheck Cond: (film_id = film.film_id)

                          Heap Blocks: exact=10

                          ->  Bitmap Index Scan on idx_fk_film_id  (cost=0.00..4.32 rows=5 width=0) (actual time=0.015..0.015 rows=10 loops=1)

                                Index Cond: (film_id = film.film_id)

                    ->  Index Scan using actor_pkey on actor  (cost=0.14..0.17 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=10)

                          Index Cond: (actor_id = film_actor.actor_id)

Planning time: 0.339 ms

Execution time: 0.131 ms

EXPLAIN