Postgres 12

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 table days
(
  id integer not null primary key,
  day character varying(9) not null
);
create table event
(
  day_id integer not null references days,
  event_id integer not null, -- references ???? 
  event_type varchar(10) not null check (event_type in ('visit', 'pass'))
);
insert into days values 
(1,'Monday'),
(2,'Tuesday'),
(3,'Wednesday'),
(4,'Thursday');
4 rows affected

insert into event values 
(1, 11, 'visit'),
(1, 13, 'visit'),
(1, 19, 'visit'),
(1, 13, 'pass'),
(1, 17, 'pass'),
(2, 7, 'visit'),
(2, 9, 'visit'),
(2, 11, 'pass'),
(2, 13, 'pass'),
(2, 19, 'pass'),
(3, 2, 'visit'),
(3, 5, 'visit'),
(3, 21, 'visit'),
(3, 21, 'pass'),
(3, 27, 'pass'),
(4, 3, 'visit'),
(4, 11, 'visit'),
(4, 39, 'visit'),
(4, 21, 'pass'),
(4, 19, 'pass');
20 rows affected

select array_agg(event_id) 
from event
where day_id in (1,2)
  and event_type = 'visit';


array_agg


{11,13,19,7,9}


select array_agg(event_id) 
from event
where day_id in (3,5)
  and event_type = 'pass';


array_agg


{21,27}