Postgres 16

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?.
select setseed(.1);
create table route(
  route_id int primary key,
  acceptance_status text
);
create table shipment(
  shipment_id int primary key,
  description text
);
create table route_to_shipment(
  shipment_id int references shipment(shipment_id),
  route_id int references route(route_id) 
);

insert into route select g,('{prep,sent,pending,accepted,rejected}'::text[])[1+(random()*4)::int]
from generate_series(1,2e3)_(g);

insert into shipment select g,gen_random_uuid()
from generate_series(1,40e3)_(g);

insert into route_to_shipment select * from (select distinct shipment_id,route_id
from route,shipment limit 40e3) order by random() limit 20e3;

setseed

SELECT 1

CREATE TABLE

CREATE TABLE

CREATE TABLE

INSERT 0 2000

INSERT 0 40000

INSERT 0 20000

prepare using_route_id as 
  SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT JOIN LATERAL (
    SELECT rts.route_id, array_agg(rts.shipment_id) shipment_ids
    FROM route_to_shipment rts
    where rts.route_id = r.route_id
    GROUP BY rts.route_id
) rts using (route_id)
group by 1 order by 1,2;

explain analyze verbose execute using_route_id;
execute using_route_id;

PREPARE

QUERY PLAN

Sort  (cost=41.60..42.10 rows=200 width=40) (actual time=0.652..0.653 rows=5 loops=1)

  Output: r.acceptance_status, (count(*))

  Sort Key: r.acceptance_status, (count(*))

  Sort Method: quicksort  Memory: 25kB

  ->  HashAggregate  (cost=31.95..33.95 rows=200 width=40) (actual time=0.617..0.619 rows=5 loops=1)

        Output: r.acceptance_status, count(*)

        Group Key: r.acceptance_status

        Batches: 1  Memory Usage: 40kB

        ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=32) (actual time=0.013..0.207 rows=2000 loops=1)

              Output: r.route_id, r.acceptance_status

Planning Time: 0.353 ms

Execution Time: 0.705 ms

EXPLAIN

acceptance_status
count

accepted
502

pending
468

prep
254

rejected
270

sent
506

SELECT 5

prepare plain_join as 
  SELECT
r.acceptance_status, count(*) as count
FROM route r
LEFT join (
    SELECT distinct rts.route_id
    FROM route_to_shipment rts
) rts using (route_id)
group by 1 order by 1,2;

explain analyze verbose execute plain_join;
execute plain_join;

PREPARE

QUERY PLAN

Sort  (cost=41.60..42.10 rows=200 width=40) (actual time=0.552..0.553 rows=5 loops=1)

  Output: r.acceptance_status, (count(*))

  Sort Key: r.acceptance_status, (count(*))

  Sort Method: quicksort  Memory: 25kB

  ->  HashAggregate  (cost=31.95..33.95 rows=200 width=40) (actual time=0.545..0.547 rows=5 loops=1)

        Output: r.acceptance_status, count(*)

        Group Key: r.acceptance_status

        Batches: 1  Memory Usage: 40kB

        ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=32) (actual time=0.007..0.151 rows=2000 loops=1)

              Output: r.route_id, r.acceptance_status

Planning Time: 0.056 ms

Execution Time: 0.569 ms

EXPLAIN

acceptance_status
count

accepted
502

pending
468

prep
254

rejected
270

sent
506

SELECT 5

prepare no_join as 
SELECT r.acceptance_status, count(*) as count
FROM route r
group by 1 order by 1,2;

explain analyze verbose execute no_join;
execute no_join;

PREPARE

QUERY PLAN

Sort  (cost=41.60..42.10 rows=200 width=40) (actual time=0.552..0.553 rows=5 loops=1)

  Output: acceptance_status, (count(*))

  Sort Key: r.acceptance_status, (count(*))

  Sort Method: quicksort  Memory: 25kB

  ->  HashAggregate  (cost=31.95..33.95 rows=200 width=40) (actual time=0.546..0.547 rows=5 loops=1)

        Output: acceptance_status, count(*)

        Group Key: r.acceptance_status

        Batches: 1  Memory Usage: 40kB

        ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=32) (actual time=0.007..0.150 rows=2000 loops=1)

              Output: route_id, acceptance_status

Planning Time: 0.040 ms

Execution Time: 0.569 ms

EXPLAIN

acceptance_status
count

accepted
502

pending
468

prep
254

rejected
270

sent
506

SELECT 5