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 mytable (date date, value int);
insert into mytable (date, value) values
  (date '2017-11-26', 5),
  (date '2017-11-26', 5),
  (date '2017-11-26', 5),
  (date '2017-11-28', 20),
  (date '2017-11-28', 5),
  (date '2018-01-07', 200),
  (date '2018-01-07', 5),
  (date '2018-01-07', 20),
  (date '2018-01-15', 5),
  (date '2018-01-16', 50);
10 rows affected

select
  date,
  value,
  sum(value) over (order by date range between unbounded preceding and current row) as sm,
  count(*) over (order by date range between unbounded preceding and current row) as cnt,
  sum(value) over (order by date range between unbounded preceding and current row) /
  count(*) over (order by date range between unbounded preceding and current row) as av
from mytable;


date
value
sm
cnt
av


2017-11-26
5
15
3
5

2017-11-26
5
15
3
5

2017-11-26
5
15
3
5

2017-11-28
20
40
5
8

2017-11-28
5
40
5
8

2018-01-07
200
265
8
33

2018-01-07
5
265
8
33

2018-01-07
20
265
8
33

2018-01-15
5
270
9
30

2018-01-16
50
320
10
32


select
  date,
  sum(sum_value) over (order by date) as running_sum,
  sum(cnt) over (order by date) as running_count,
  sum(sum_value) over (order by date) /
  sum(cnt) over (order by date) as running_average
from
(
  select date, sum(value) as sum_value, count(*) as cnt 
  from mytable
  group by date
) agg
order by date;


date
running_sum
running_count
running_average


2017-11-26
15
3
5.0000000000000000

2017-11-28
40
5
8.0000000000000000

2018-01-07
265
8
33.1250000000000000

2018-01-15
270
9
30.0000000000000000

2018-01-16
320
10
32.0000000000000000

date	value	sm	cnt	av
2017-11-26	5	15	3	5
2017-11-26	5	15	3	5
2017-11-26	5	15	3	5
2017-11-28	20	40	5	8
2017-11-28	5	40	5	8
2018-01-07	200	265	8	33
2018-01-07	5	265	8	33
2018-01-07	20	265	8	33
2018-01-15	5	270	9	30
2018-01-16	50	320	10	32

date	running_sum	running_count	running_average
2017-11-26	15	3	5.0000000000000000
2017-11-28	40	5	8.0000000000000000
2018-01-07	265	8	33.1250000000000000
2018-01-15	270	9	30.0000000000000000
2018-01-16	320	10	32.0000000000000000