Postgres 16

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 (
  name text primary key,
  price integer not null,
  qty integer not null
);

CREATE TABLE


insert into items (name, price, qty) values
  ('A', 3, 30), ('B', 5, 3), ('C', 5, 3), ('D', 6, 20);

INSERT 0 4


  select
    name,
    price,
    qty,
    sum(qty) over w as "running purchased",
    sum(price * qty) over w as "running cost",
    lead(name) over w as "next option",
    lead(price) over w as "next price",
    lead(qty) over w as "next option"
  from items
  window w as (order by price, name asc rows unbounded preceding)


name
price
qty
running purchased
running cost
next option
next price
next option


A
3
30
30
90
B
5
3

B
5
3
33
105
C
5
3

C
5
3
36
120
D
6
20

D
6
20
56
240
null
null
null



SELECT 4


with running_total as (
  select
    name,
    price,
    qty,
    sum(qty) over w as "running purchased",
    sum(price * qty) over w as "running cost",
    lead(name) over w as "next option",
    lead(price) over w as "next price",
    lead(qty) over w as "next option"
  from items
  window w as (order by price, name asc rows unbounded preceding)
),
final_purchase as (
  select 
    *, 
    floor( (100 - "running cost") / "next price" ) as "next purchased"
  from running_total
  where "running cost" <= 100
)
select
  *,
  "next purchased" * "next price" as "next cost",
  "running purchased" + "next purchased" as "total purchased",
  "running cost" + ("next purchased" * "next price") as "total spent",
  100 - ("running cost" + ("next purchased" * "next price")) as "remaining money"
from final_purchase


name
price
qty
running purchased
running cost
next option
next price
next option
next purchased
next cost
total purchased
total spent
remaining money


A
3
30
30
90
B
5
3
2
10
32
100
0



SELECT 1

name	price	qty	running purchased	running cost	next option	next price	next option
A	3	30	30	90	B	5	3
B	5	3	33	105	C	5	3
C	5	3	36	120	D	6	20
D	6	20	56	240	null	null	null