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 tbl_requests (requestid int, requesteditems text);

CREATE TABLE


insert into tbl_requests
values
  (1234567, 'laptop,monitor,mouse'),
  (1234568, 'laptop,dock'),
  (1234569, 'monitor,keyboard,mouse'),
  (1234570, 'monitor,dock,keyboard');

INSERT 0 4


create table tbl_storage(storageid int, item text);

CREATE TABLE


insert into tbl_storage
values
  (123, 'laptop'),
  (123, 'monitor'),
  (123, 'mouse'),
  (123, 'keyboard'),
  (154, 'laptop'),
  (154, 'dock');

INSERT 0 6


with storage as (
  select storageid, array_agg(item) items
  from tbl_storage
  group by storageid 
), requests as (
  select requestid, string_to_array(requesteditems,',') as items
  from tbl_requests
)
select r.requestid, s.storageid
from requests r
  join storage s on s.items @> r.items;


requestid
storageid


1234567
123

1234568
154

1234569
123



SELECT 3