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?.

create table items (id int, name  varchar(100), ref varchar(2));
insert into items values
(1,'item 1','x2')
,(2,'item 2','x3');

create table options (id int,  option_id int, item_id int);
insert into options values
(1  ,1, 1)
,(2  , 2, 1)
,(2  , 3, 1)
,(2  , 1, 2)
,(2  , 3, 2);


CREATE TABLE



INSERT 0 2



CREATE TABLE



INSERT 0 5


select * from items i 
join ( select item_id , ARRAY_AGG(option_id order by option_id) optionids
       from options 
       group by item_id) o
       on i.id = o.item_id
where ref = 'x2'
and optionids <@ array[1,3,2]


id
name
ref
item_id
optionids


1
item 1
x2
1
{1,2,3}



SELECT 1


select * from items i 
join ( select item_id , ARRAY_AGG(option_id order by option_id) optionids
       from options 
       group by item_id) o
       on i.id = o.item_id
where ref = 'x2'
and optionids <@ sort(array[1,2])


id
name
ref
item_id
optionids



SELECT 0