Postgres 15

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 delivery_note_item (id BIGINT PRIMARY KEY, stock_item_id BIGINT, quantity BIGINT, received TIMESTAMP);
INSERT INTO delivery_note_item (id, stock_item_id, quantity, received) 
VALUES 
(1, 1, 5, '2023-08-01 00:00:00'),
(2, 1, 3, '2023-08-02 00:00:00'),
(3, 1, 9, '2023-08-03 00:00:00'), 
(4, 1, 8, '2023-08-04 00:00:00');

CREATE TABLE



INSERT 0 4


select id, sum(quantity) over (partition by stock_item_id order by received) as FIFO_cumul,
           sum(quantity) over (partition by stock_item_id order by received DESC) as LIFO_cumul
from delivery_note_item


id
fifo_cumul
lifo_cumul


1
5
25

2
8
20

3
17
17

4
25
8



SELECT 4


select *
from (
  select *, sum(quantity) over (partition by stock_item_id order by received) - quantity as FIFO_running,
            sum(quantity) over (partition by stock_item_id order by received DESC) - quantity as LIFO_running
  from delivery_note_item
) as s
where stock_item_id = 1 and fifo_running <= 10


id
stock_item_id
quantity
received
fifo_running
lifo_running


1
1
5
2023-08-01 00:00:00
0
20

2
1
3
2023-08-02 00:00:00
5
17

3
1
9
2023-08-03 00:00:00
8
8



SELECT 3


select *
from (
  select *, sum(quantity) over (partition by stock_item_id order by received) - quantity as FIFO_running,
            sum(quantity) over (partition by stock_item_id order by received DESC) - quantity as LIFO_running
  from delivery_note_item
) as s
where stock_item_id = 1 and LIFO_running <= 10


id
stock_item_id
quantity
received
fifo_running
lifo_running


3
1
9
2023-08-03 00:00:00
8
8

4
1
8
2023-08-04 00:00:00
17
0



SELECT 2

id	stock_item_id	quantity	received	fifo_running	lifo_running
1	1	5	2023-08-01 00:00:00	0	20
2	1	3	2023-08-02 00:00:00	5	17
3	1	9	2023-08-03 00:00:00	8	8