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=1.145..1.147 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=1.076..1.079 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.021..0.356 rows=2000 loops=1)

              Output: r.route_id, r.acceptance_status

Planning Time: 0.746 ms

Execution Time: 1.227 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=1.000..1.001 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.990..0.992 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.012..0.280 rows=2000 loops=1)

              Output: r.route_id, r.acceptance_status

Planning Time: 0.101 ms

Execution Time: 1.031 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=1.048..1.049 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=1.036..1.039 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.010..0.264 rows=2000 loops=1)

              Output: route_id, acceptance_status

Planning Time: 0.060 ms

Execution Time: 1.075 ms

EXPLAIN

acceptance_status
count

accepted
502

pending
468

prep
254

rejected
270

sent
506

SELECT 5

prepare on_true 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 on true
group by 1 order by 1,2;

explain analyze verbose execute on_true;
execute on_true;

PREPARE

QUERY PLAN

Incremental Sort  (cost=475713.66..475735.06 rows=200 width=40) (actual time=2875.369..2875.372 rows=5 loops=1)

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

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

  Presorted Key: r.acceptance_status

  Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB

  ->  GroupAggregate  (cost=475713.59..475726.06 rows=200 width=40) (actual time=2875.114..2875.325 rows=5 loops=1)

        Output: r.acceptance_status, count(*)

        Group Key: r.acceptance_status

        ->  Sort  (cost=475713.59..475717.08 rows=1397 width=32) (actual time=2874.996..2875.101 rows=2000 loops=1)

              Output: r.acceptance_status

              Sort Key: r.acceptance_status

              Sort Method: quicksort  Memory: 86kB

              ->  Nested Loop Left Join  (cost=0.00..475640.60 rows=1397 width=32) (actual time=65.259..2873.545 rows=2000 loops=1)

                    Output: r.acceptance_status

                    ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=36) (actual time=0.017..0.422 rows=2000 loops=1)

                          Output: r.route_id, r.acceptance_status

                    ->  GroupAggregate  (cost=0.00..340.44 rows=1 width=36) (actual time=1.435..1.435 rows=1 loops=2000)

                          Output: rts.route_id, NULL::integer[]

                          ->  Seq Scan on public.route_to_shipment rts  (cost=0.00..340.43 rows=101 width=4) (actual time=0.166..1.432 rows=10 loops=2000)

                                Output: rts.shipment_id, rts.route_id

                                Filter: (rts.route_id = r.route_id)

                                Rows Removed by Filter: 19990

Planning Time: 0.235 ms

JIT:

  Functions: 11

  Options: Inlining false, Optimization false, Expressions true, Deforming true

  Timing: Generation 1.664 ms, Inlining 0.000 ms, Optimization 3.992 ms, Emission 57.856 ms, Total 63.512 ms

Execution Time: 3467.651 ms

EXPLAIN

acceptance_status
count

accepted
502

pending
468

prep
254

rejected
270

sent
506

SELECT 5

prepare on_true2 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
    
    GROUP BY rts.route_id
) rts on true
where rts.route_id = r.route_id
group by 1 order by 1,2;

explain analyze verbose execute on_true2;
execute on_true2;

PREPARE

QUERY PLAN

Sort  (cost=386.22..386.72 rows=200 width=40) (actual time=6.804..6.806 rows=5 loops=1)

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

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

  Sort Method: quicksort  Memory: 25kB

  ->  HashAggregate  (cost=376.57..378.57 rows=200 width=40) (actual time=6.781..6.784 rows=5 loops=1)

        Output: r.acceptance_status, count(*)

        Group Key: r.acceptance_status

        Batches: 1  Memory Usage: 40kB

        ->  Hash Join  (cost=346.93..375.57 rows=200 width=32) (actual time=5.816..6.415 rows=2000 loops=1)

              Output: r.acceptance_status

              Inner Unique: true

              Hash Cond: (r.route_id = rts.route_id)

              ->  Seq Scan on public.route r  (cost=0.00..24.97 rows=1397 width=36) (actual time=0.012..0.187 rows=2000 loops=1)

                    Output: r.route_id, r.acceptance_status

              ->  Hash  (cost=344.43..344.43 rows=200 width=4) (actual time=5.785..5.786 rows=2000 loops=1)

                    Output: rts.route_id

                    Buckets: 2048 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 87kB

                    ->  Subquery Scan on rts  (cost=340.43..344.43 rows=200 width=4) (actual time=5.055..5.493 rows=2000 loops=1)

                          Output: rts.route_id

                          ->  HashAggregate  (cost=340.43..342.43 rows=200 width=36) (actual time=5.054..5.313 rows=2000 loops=1)

                                Output: rts_1.route_id, NULL::integer[]

                                Group Key: rts_1.route_id

                                Batches: 1  Memory Usage: 257kB

                                ->  Seq Scan on public.route_to_shipment rts_1  (cost=0.00..290.14 rows=20114 width=4) (actual time=0.009..1.552 rows=20000 loops=1)

                                      Output: rts_1.shipment_id, rts_1.route_id

Planning Time: 0.357 ms

Execution Time: 6.904 ms

EXPLAIN

acceptance_status
count

accepted
502

pending
468

prep
254

rejected
270

sent
506

SELECT 5