Postgres 14

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 relate(
	id varchar(5),
	parentid varchar(5),
	mount int
);

CREATE TABLE pro(
	id varchar(5),
	stock int
	
);

INSERT INTO relate (id,parentid,mount)
VALUES ('1-1', '1',1),('1-2', '1',1),('1-3', '1',1);

INSERT INTO pro (id,stock)
VALUES ('1-1',1500),('1-2',11793),('1-3',1934);
3 rows affected

3 rows affected

select *
  from relate;


id
parentid
mount


1-1
1
1

1-2
1
1

1-3
1
1


select *
  from pro;


id
stock


1-1
1500

1-2
11793

1-3
1934


select a.Id,a.ParentId,a.mount,b.stock, floor(b.stock  / a.mount) as "prod"
  from relate a 
  left join pro b on a.Id = b.id
 where a.parentid = '1';


id
parentid
mount
stock
prod


1-1
1
1
1500
1500

1-2
1
1
11793
11793

1-3
1
1
1934
1934


with t1 as (
select a.Id,a.ParentId,a.mount,b.stock, floor(b.stock  / a.mount) as "prod"
  from relate a 
  left join pro b on a.Id = b.id
 where a.parentid = '1'
)
select min(prod)
  from t1;


min


1500