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

select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


CREATE TABLE table_1 (
    user_id int,
    date date,
    actual int,
    scheduled int
);

INSERT INTO table_1 (user_id, date, actual, scheduled)
VALUES (1, '2022-06-01', 1, 100),
 (1, '2022-06-02', 2, 101),
 (1, '2022-06-03', 3, 103),
 (1, '2022-06-04', 4, 104),
 (1, '2022-06-05', 5, 105),
 (2, '2022-06-06', 6, 106),
 (2, '2022-06-07', 7, 107),
 (2, '2022-06-08', 8, 108),
 (2, '2022-06-09', 9, 109),
 (2, '2022-06-10', 10, 110);
10 rows affected

SELECT * FROM table_1


user_id
date
actual
scheduled


1
2022-06-01
1
100

1
2022-06-02
2
101

1
2022-06-03
3
103

1
2022-06-04
4
104

1
2022-06-05
5
105

2
2022-06-06
6
106

2
2022-06-07
7
107

2
2022-06-08
8
108

2
2022-06-09
9
109

2
2022-06-10
10
110


SELECT
    user_id,
    date,
    actual,
    scheduled,
    SUM(CASE WHEN date <= '2022-06-6' THEN actual ELSE scheduled END) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM table_1    
    


user_id
date
actual
scheduled
sum


1
2022-06-01
1
100
1

1
2022-06-02
2
101
3

1
2022-06-03
3
103
6

1
2022-06-04
4
104
10

1
2022-06-05
5
105
15

2
2022-06-06
6
106
21

2
2022-06-07
7
107
128

2
2022-06-08
8
108
236

2
2022-06-09
9
109
345

2
2022-06-10
10
110
455

user_id	date	actual	scheduled
1	2022-06-01	1	100
1	2022-06-02	2	101
1	2022-06-03	3	103
1	2022-06-04	4	104
1	2022-06-05	5	105
2	2022-06-06	6	106
2	2022-06-07	7	107
2	2022-06-08	8	108
2	2022-06-09	9	109
2	2022-06-10	10	110