Postgres 12

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 tmp
(
  x int
);

CREATE TABLE


insert into tmp values (1), (2), (3), (5), (8), (13), (21);

INSERT 0 7


select x,
       count(*) over w as w_count,
       avg(x) over w   as avg_x,
       case
           when lag(x) over w is null then x
           when lag(x, 2) over w is null then (x + lag(x) over w) / 2.
           when lag(x, 3) over w is null then lag(x) over w
           else (lag(x, 2) over w + lag(x) over w) / 2.
           end
from tmp t
    window w as (rows between 3 preceding and current row)
order by 1;


x
w_count
avg_x
case


1
1
1.00000000000000000000
1

2
2
1.5000000000000000
1.5000000000000000

3
3
2.0000000000000000
2

5
4
2.7500000000000000
2.5000000000000000

8
4
4.5000000000000000
4.0000000000000000

13
4
7.2500000000000000
6.5000000000000000

21
4
11.7500000000000000
10.5000000000000000



SELECT 7

x	w_count	avg_x	case
1	1	1.00000000000000000000	1
2	2	1.5000000000000000	1.5000000000000000
3	3	2.0000000000000000	2
5	4	2.7500000000000000	2.5000000000000000
8	4	4.5000000000000000	4.0000000000000000
13	4	7.2500000000000000	6.5000000000000000
21	4	11.7500000000000000	10.5000000000000000