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 IF NOT EXISTS dre
(
    idmov integer,
    companynumber integer,
    idproduct integer,
    dtdate date,
    nrtank integer,
    dailybalance numeric,
    startbalance numeric,
    endbalance numeric
)

CREATE TABLE


INSERT INTO dre values 
(1, 104, 10, '30/09/2023', 5, 0, NULL, 7600),
(2, 104, 10, '01/10/2023', 5,-1089.42, NULL, NULL),
(3, 104, 10, '02/10/2023', 5,-404.62, NULL, NULL),
(4, 104, 10, '03/10/2023', 5,-470.57, NULL, NULL),
(5, 104, 10, '04/10/2023', 5,-604.97, NULL, NULL),
(6, 104, 10, '05/10/2023', 5,10381.23, NULL, NULL),
(7, 104, 10, '06/10/2023', 5,-593.54, NULL, NULL),
(8, 104, 10, '07/10/2023', 5,-713.27, NULL, NULL),
(9, 104, 10, '08/10/2023', 5,-995.01, NULL, NULL),
(10, 104, 10, '09/10/2023', 5,-736.25, NULL, NULL)

INSERT 0 10


WITH CTE AS (SELECT idmov, 
                    companynumber,
                    idproduct, 
                    dtdate,
                    nrtank,
                    dailybalance,
                    startbalance,
                    SUM(COALESCE(endbalance,0)+dailybalance)
                       OVER w1 AS endbalance
             FROM dre
             WINDOW w1 AS (PARTITION BY companynumber,idproduct,nrtank 
                           ORDER BY dtdate))
SELECT idmov, 
       companynumber,
       idproduct, 
       dtdate,
       nrtank,
       dailybalance,
       LAG(endbalance)OVER w2 AS startbalance,
       endbalance
FROM CTE
WINDOW w2 AS (PARTITION BY companynumber,idproduct,nrtank 
              ORDER BY dtdate);


idmov
companynumber
idproduct
dtdate
nrtank
dailybalance
startbalance
endbalance


1
104
10
2023-09-30
5
0
null
7600

2
104
10
2023-10-01
5
-1089.42
7600
6510.58

3
104
10
2023-10-02
5
-404.62
6510.58
6105.96

4
104
10
2023-10-03
5
-470.57
6105.96
5635.39

5
104
10
2023-10-04
5
-604.97
5635.39
5030.42

6
104
10
2023-10-05
5
10381.23
5030.42
15411.65

7
104
10
2023-10-06
5
-593.54
15411.65
14818.11

8
104
10
2023-10-07
5
-713.27
14818.11
14104.84

9
104
10
2023-10-08
5
-995.01
14104.84
13109.83

10
104
10
2023-10-09
5
-736.25
13109.83
12373.58



SELECT 10

idmov	companynumber	idproduct	dtdate	nrtank	dailybalance	startbalance	endbalance
1	104	10	2023-09-30	5	0	null	7600
2	104	10	2023-10-01	5	-1089.42	7600	6510.58
3	104	10	2023-10-02	5	-404.62	6510.58	6105.96
4	104	10	2023-10-03	5	-470.57	6105.96	5635.39
5	104	10	2023-10-04	5	-604.97	5635.39	5030.42
6	104	10	2023-10-05	5	10381.23	5030.42	15411.65
7	104	10	2023-10-06	5	-593.54	15411.65	14818.11
8	104	10	2023-10-07	5	-713.27	14818.11	14104.84
9	104	10	2023-10-08	5	-995.01	14104.84	13109.83
10	104	10	2023-10-09	5	-736.25	13109.83	12373.58