Postgres 13

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 version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE supervisor
(
    uuid uuid    not null
        constraint pkey_supervisor
            primary key,
    name varchar not null
);

CREATE TABLE activity_planning
(
    uuid           uuid      not null
        constraint pkey_activity_planning
            primary key,
    type           varchar   not null,
    activity_start timestamp not null,
    activity_end   timestamp not null,
    group_size     integer,
    cancelled      boolean default false
);

create index idx_activity_planning_start
    on activity_planning (activity_start);

create table scheduled_activity
(
    uuid                 uuid      not null
        constraint pkey_scheduled_activity
            primary key,
    activity_planning_id uuid      not null
        constraint fk_scheduled_activity_to_planned_activity
            references activity_planning
            deferrable initially deferred,
    planned_start        timestamp not null,
    planned_end          timestamp not null,
    supervisors          integer   not null
);

create index idx_scheduled_activity_activity_planning
    on scheduled_activity (activity_planning_id);

create table supervised_activity
(
    uuid                  uuid not null
        constraint pkey_supervised_activity
            primary key,
    scheduled_activity_id uuid not null
        constraint fk_supervised_activity_to_scheduled_activity
            references scheduled_activity,
    supervisor_id         uuid
);

create index idx_supervised_activity_scheduled_activity
    on supervised_activity (scheduled_activity_id);

create index idx_supervised_activity_supervisor
    on supervised_activity (supervisor_id);

insert into supervisor(uuid, name) VALUES ('00000000-0000-0000-0000-000000000000', 'Tom');

insert into supervisor(uuid, name)
SELECT gen_random_uuid() as uuid,
       md5(random()::text)
FROM generate_series(1, 10) s(i);

insert into activity_planning (uuid, type, activity_start, activity_end, group_size)
select sub.uuid                                 as uuid,
       md5(random()::text)                      as type,
       sub.start_time                           as activity_start,
       sub.start_time + INTERVAL '+ 30 minutes' as activity_end,
       floor(random() * (40) + 1)::int          as group_size
from (
         SELECT gen_random_uuid()                  as uuid,
                now() + i * INTERVAL '+ 2 minutes' as start_time
         from generate_series(1, 100000) s(i)
     ) sub;


insert into scheduled_activity (uuid, activity_planning_id, planned_start, planned_end, supervisors)
SELECT gen_random_uuid(),
       activity.uuid,
       activity.activity_start,
       activity.activity_end,
       ceil((activity.group_size::decimal / 10))
FROM activity_planning activity;

insert into supervised_activity (uuid, scheduled_activity_id, supervisor_id)
SELECT  gen_random_uuid(), exploded.uuid, exploded.supervisor FROM (
                  SELECT scheduled_activity.uuid,
                         generate_series(1, scheduled_activity.supervisors),
                         (SELECT s.uuid
                          FROM supervisor s
                          ORDER BY random() + scheduled_activity.supervisors
                          LIMIT 1) as supervisor
                  from scheduled_activity
                  ) exploded
1 rows affected

10 rows affected

100000 rows affected

100000 rows affected

249790 rows affected

ANALYZE
CREATE VIEW supervisor_agenda AS (
select su.supervisor_id as supervisor_id,
       lag(sch.planned_start, 1, '1970-01-01 00:00:00') over w as previous_activity_start,
       lag(act.type) over w as previous_activity_type,
       act.type as activity_type,
       lead(sch.planned_end, 1, '9999-12-31 23:59:59') over w as next_activity_end,
       lead(act.type) over w as next_activity_type,
       su.scheduled_activity_id as scheduled_activity_id,
       su.uuid as supervised_activity_id,
       sch.planned_start as activity_start,
       sch.planned_end as activity_end
from supervised_activity su
         inner join scheduled_activity sch
                    on su.scheduled_activity_id = sch.uuid
         inner join activity_planning act
                    on sch.activity_planning_id = act.uuid
    window w as (partition by su.supervisor_id order by sch.planned_start))
CREATE OR REPLACE FUNCTION sup_agenda(sup_id uuid, activ uuid)
 RETURNS table(
        supervisor_id uuid,
        previous_activity_start timestamp,
        previous_activity_type varchar,
        activity_type varchar,
        next_activity_end timestamp,
        next_activity_type varchar,
        scheduled_activity_id uuid,
        supervised_activity_id uuid,
        activity_start timestamp,
        activity_end timestamp
)
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$
select su.supervisor_id as supervisor_id,
       lag(sch.planned_start, 1, '1970-01-01 00:00:00') over w as previous_activity_start,
       lag(act.type) over w as previous_activity_type,
       act.type as activity_type,
       lead(sch.planned_end, 1, '9999-12-31 23:59:59') over w as next_activity_end,
       lead(act.type) over w as next_activity_type,
       su.scheduled_activity_id as scheduled_activity_id,
       su.uuid as supervised_activity_id,
       sch.planned_start as activity_start,
       sch.planned_end as activity_end
from supervised_activity su
         inner join scheduled_activity sch
                    on su.scheduled_activity_id = sch.uuid
         inner join activity_planning act
                    on sch.activity_planning_id = act.uuid
WHERE 
su.scheduled_activity_id = activ
AND su.supervisor_id = sup_id
    window w as (partition by su.supervisor_id order by sch.planned_start);
$function$
EXPLAIN (ANALYZE)
SELECT * FROM supervisor_agenda WHERE 
scheduled_activity_id = '00000000-0000-0000-0000-000000000000'
AND supervisor_id = '00000000-0000-0000-0000-000000000000'


QUERY PLAN


Subquery Scan on supervisor_agenda  (cost=10912.75..11824.31 rows=3 width=177) (actual time=158.094..158.099 rows=0 loops=1)

  Filter: (supervisor_agenda.scheduled_activity_id = '00000000-0000-0000-0000-000000000000'::uuid)

  Rows Removed by Filter: 22646

  ->  WindowAgg  (cost=10912.75..11539.45 rows=22789 width=177) (actual time=131.609..156.511 rows=22646 loops=1)

        ->  Sort  (cost=10912.75..10969.72 rows=22789 width=97) (actual time=131.590..133.341 rows=22646 loops=1)

              Sort Key: sch.planned_start

              Sort Method: quicksort  Memory: 3770kB

              ->  Hash Join  (cost=6326.39..9263.28 rows=22789 width=97) (actual time=84.010..122.593 rows=22646 loops=1)

                    Hash Cond: (act.uuid = sch.activity_planning_id)

                    ->  Seq Scan on activity_planning act  (cost=0.00..2334.00 rows=100000 width=49) (actual time=0.014..16.575 rows=100000 loops=1)

                    ->  Hash  (cost=6041.52..6041.52 rows=22789 width=80) (actual time=83.961..83.965 rows=22646 loops=1)

                          Buckets: 32768  Batches: 1  Memory Usage: 2733kB

                          ->  Hash Join  (cost=3157.63..6041.52 rows=22789 width=80) (actual time=39.215..75.245 rows=22646 loops=1)

                                Hash Cond: (sch.uuid = su.scheduled_activity_id)

                                ->  Seq Scan on scheduled_activity sch  (cost=0.00..2031.00 rows=100000 width=48) (actual time=0.015..14.688 rows=100000 loops=1)

                                ->  Hash  (cost=2872.77..2872.77 rows=22789 width=48) (actual time=39.164..39.166 rows=22646 loops=1)

                                      Buckets: 32768  Batches: 1  Memory Usage: 2026kB

                                      ->  Bitmap Heap Scan on supervised_activity su  (cost=252.91..2872.77 rows=22789 width=48) (actual time=1.265..30.489 rows=22646 loops=1)

                                            Recheck Cond: (supervisor_id = '00000000-0000-0000-0000-000000000000'::uuid)

                                            Heap Blocks: exact=2335

                                            ->  Bitmap Index Scan on idx_supervised_activity_supervisor  (cost=0.00..247.21 rows=22789 width=0) (actual time=0.999..0.999 rows=22646 loops=1)

                                                  Index Cond: (supervisor_id = '00000000-0000-0000-0000-000000000000'::uuid)

Planning Time: 1.526 ms

Execution Time: 158.609 ms


EXPLAIN (ANALYZE)
select su.supervisor_id as supervisor_id,
       lag(sch.planned_start, 1, '1970-01-01 00:00:00') over w as previous_activity_start,
       lag(act.type) over w as previous_activity_type,
       act.type as activity_type,
       lead(sch.planned_end, 1, '9999-12-31 23:59:59') over w as next_activity_end,
       lead(act.type) over w as next_activity_type,
       su.scheduled_activity_id as scheduled_activity_id,
       su.uuid as supervised_activity_id,
       sch.planned_start as activity_start,
       sch.planned_end as activity_end
from supervised_activity su
         inner join scheduled_activity sch
                    on su.scheduled_activity_id = sch.uuid
         inner join activity_planning act
                    on sch.activity_planning_id = act.uuid
WHERE 
su.scheduled_activity_id = '00000000-0000-0000-0000-000000000000'
AND su.supervisor_id = '00000000-0000-0000-0000-000000000000'
    window w as (partition by su.supervisor_id order by sch.planned_start)
    


QUERY PLAN


WindowAgg  (cost=33.06..33.09 rows=1 width=177) (actual time=0.028..0.029 rows=0 loops=1)

  ->  Sort  (cost=33.06..33.06 rows=1 width=97) (actual time=0.027..0.028 rows=0 loops=1)

        Sort Key: sch.planned_start

        Sort Method: quicksort  Memory: 25kB

        ->  Nested Loop  (cost=5.28..33.05 rows=1 width=97) (actual time=0.024..0.025 rows=0 loops=1)

              ->  Nested Loop  (cost=4.86..24.61 rows=1 width=80) (actual time=0.023..0.024 rows=0 loops=1)

                    ->  Bitmap Heap Scan on supervised_activity su  (cost=4.44..16.17 rows=1 width=48) (actual time=0.023..0.023 rows=0 loops=1)

                          Recheck Cond: (scheduled_activity_id = '00000000-0000-0000-0000-000000000000'::uuid)

                          Filter: (supervisor_id = '00000000-0000-0000-0000-000000000000'::uuid)

                          ->  Bitmap Index Scan on idx_supervised_activity_scheduled_activity  (cost=0.00..4.44 rows=3 width=0) (actual time=0.021..0.021 rows=0 loops=1)

                                Index Cond: (scheduled_activity_id = '00000000-0000-0000-0000-000000000000'::uuid)

                    ->  Index Scan using pkey_scheduled_activity on scheduled_activity sch  (cost=0.42..8.44 rows=1 width=48) (never executed)

                          Index Cond: (uuid = '00000000-0000-0000-0000-000000000000'::uuid)

              ->  Index Scan using pkey_activity_planning on activity_planning act  (cost=0.42..8.44 rows=1 width=49) (never executed)

                    Index Cond: (uuid = sch.activity_planning_id)

Planning Time: 0.445 ms

Execution Time: 0.076 ms