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 daily_stock (
  "id" INTEGER,
  "product_id" INTEGER,
  "product_type_id" INTEGER,
  "closing_stock" INTEGER,
  "created_dttm" DATE
);

INSERT INTO daily_stock ("id", "product_id", "product_type_id", "closing_stock", "created_dttm") VALUES
  ('1', '2', '1', '0', '21-Nov-21'),
  ('2', '2', '2', '9', '21-Nov-21'),
  ('3', '2', '3', '11', '21-Nov-21'),
  ('4', '2', '1', '7', '20-Nov-21');

CREATE TABLE



INSERT 0 4


select *
from (
  select distinct on(product_id, product_type_id) * 
  from daily_stock
  where product_id = 2 and product_type_id in (1, 2, 3)
  order by product_id, product_type_id , created_dttm desc
) t
where closing_stock > 0;


id
product_id
product_type_id
closing_stock
created_dttm


2
2
2
9
2021-11-21

3
2
3
11
2021-11-21



SELECT 2


select *
from (
  select *, row_number() over (partition by product_id, product_type_id order by created_dttm desc) rn
  from daily_stock
  where product_id = 2
) t
where rn = 1 and closing_stock > 0;


id
product_id
product_type_id
closing_stock
created_dttm
rn


2
2
2
9
2021-11-21
1

3
2
3
11
2021-11-21
1



SELECT 2