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?.

select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


CREATE TABLE tableA (
   id           int primary key
 , good_id      int
 , num          int
 , created_at   date
);

CREATE TABLE tableB (
   id           int primary key
 , good_id      int
 , num_invoice  int
);

INSERT INTO tableA VALUES
  (1,1,10,'2021-09-24')
, (2,1, 5,'2021-09-25')
, (3,1, 7,'2021-09-26')
;

INSERT INTO tableB VALUES
  (1,1,12)
;
3 rows affected

1 rows affected

WITH cte1 (good_id, num_invoice) AS (
        SELECT good_id, SUM(num_invoice) AS num_invoice
          FROM tableB
         GROUP BY good_id
     )
   , cte2 AS (
       SELECT a.*, o.num_invoice
            , SUM(num) OVER (PARTITION BY a.good_id ORDER BY created_at) - o.num_invoice AS balance
         FROM tableA AS a
         JOIN cte1   AS o
           ON o.good_id = a.good_id
     )
SELECT id, good_id
     , num - GREATEST(num - GREATEST(balance, 0), 0) AS num
     , created_at
     , GREATEST(num - GREATEST(balance, 0), 0) AS invoice_num
  FROM cte2
;


id
good_id
num
created_at
invoice_num


1
1
0
2021-09-24
10

2
1
3
2021-09-25
2

3
1
7
2021-09-26
0