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 remdesivir_inventory
(
hospital_id int,
stock int,
state varchar(2)
);

CREATE TABLE remdesivir_requests
(
patient_id int,
prescribed_qty int,
state varchar(2)
);
INSERT INTO remdesivir_inventory VALUES (1, 200, 'CA');
INSERT INTO remdesivir_inventory VALUES (2, 100, 'FL');
INSERT INTO remdesivir_inventory VALUES (3, 500, 'TX');

INSERT INTO remdesivir_requests VALUES (10, 100, 'CA');
INSERT INTO remdesivir_requests VALUES (20, 200, 'FL');
INSERT INTO remdesivir_requests VALUES (30, 300, 'TX');
INSERT INTO remdesivir_requests VALUES (40, 100, 'AL');
INSERT INTO remdesivir_requests VALUES (50, 200, 'CA');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

select rr.*, ri.hospital_id
from (select rr.*, 
             sum(prescribed_qty) over (partition by state order by patient_id) as running_pq
      from remdesivir_requests rr
     ) rr join
     remdesivir_inventory ri
     on ri.state = rr.state and
        rr.running_pq <= ri.stock


patient_id
prescribed_qty
state
running_pq
hospital_id


10
100
CA
100
1

30
300
TX
300
3