Postgres 15

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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


create table revenue_table (date date, revenue int);

CREATE TABLE


insert into revenue_table
    SELECT date '2018-09-01' AS date, 1200 AS revenue UNION ALL
    SELECT date '2018-08-01' AS date, 400 AS revenue UNION ALL
    SELECT date  '2018-07-01' AS date, -1000 AS revenue UNION ALL
    SELECT  date '2018-06-01' AS date, 800 AS revenue UNION ALL
    SELECT date  '2018-05-01' AS date, 600 AS revenue UNION ALL
    SELECT date '2018-04-01' AS date, 200 AS revenue UNION ALL
    SELECT date '2018-03-01' AS date, -200 AS revenue UNION ALL
    SELECT date  '2018-02-01' AS date, 400 AS revenue UNION ALL
    SELECT date '2018-01-01' AS date, 200 AS revenue
;

INSERT 0 9


with recursive
    data as (
        select r.*, row_number() over(order by date) rn from revenue_table r
    ),
    rcte (date, old_revenue, rn, new_revenue, rest) as (
        select null::date, 0, 0::bigint, 0, 0
        union all
        select d.*, greatest(d.revenue + r.rest, 0), least(d.revenue + r.rest, 0)
        from rcte r
        inner join data d on d.rn = r.rn + 1
    )
select date, old_revenue, new_revenue 
from rcte 
where rn > 0 order by date desc;


date
old_revenue
new_revenue


2018-09-01
1200
600

2018-08-01
400
0

2018-07-01
-1000
0

2018-06-01
800
800

2018-05-01
600
600

2018-04-01
200
0

2018-03-01
-200
0

2018-02-01
400
400

2018-01-01
200
200



SELECT 9

date	old_revenue	new_revenue
2018-09-01	1200	600
2018-08-01	400	0
2018-07-01	-1000	0
2018-06-01	800	800
2018-05-01	600	600
2018-04-01	200	0
2018-03-01	-200	0
2018-02-01	400	400
2018-01-01	200	200