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

create table t (id serial, value int);
insert into t (value)
    values (4), (1), (3), (4), (4), (2), (5), (4), (6), (4)
10 rows affected

select t.*, t2.*
from t cross join lateral
     (select avg(value) as avg_5, percentile_cont(0.5) within group (order by value) as median_5
      from (select t2.*
            from t t2
            where t2.id >= t.id
            order by t2.id asc
            limit 5
           ) t2
     ) t2


id
value
avg_5
median_5


1
4
3.2000000000000000
4

2
1
2.8000000000000000
3

3
3
3.6000000000000000
4

4
4
3.8000000000000000
4

5
4
4.2000000000000000
4

6
2
4.2000000000000000
4

7
5
4.7500000000000000
4.5

8
4
4.6666666666666667
4

9
6
5.0000000000000000
5

10
4
4.0000000000000000
4


select t.*,
       avg(value) over (order by id range between current row and 4 following) as avg_5
from t


id
value
avg_5


1
4
3.2000000000000000

2
1
2.8000000000000000

3
3
3.6000000000000000

4
4
3.8000000000000000

5
4
4.2000000000000000

6
2
4.2000000000000000

7
5
4.7500000000000000

8
4
4.6666666666666667

9
6
5.0000000000000000

10
4
4.0000000000000000

id	value	avg_5	median_5
1	4	3.2000000000000000	4
2	1	2.8000000000000000	3
3	3	3.6000000000000000	4
4	4	3.8000000000000000	4
5	4	4.2000000000000000	4
6	2	4.2000000000000000	4
7	5	4.7500000000000000	4.5
8	4	4.6666666666666667	4
9	6	5.0000000000000000	5
10	4	4.0000000000000000	4