Postgres 9.5

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 9.5.25 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table t(time int,value int)
insert into t
select 1 , 3.0 union all
select 5 , 5.0 union all  
select 7 , 1.0 union all
select 9 , 8.0 union all
select 10 , 4.0 
5 rows affected

with data
   as (select time
              ,lead(time) over(order by time) as next_time
              ,value
              ,lead(value) over(order by time) as next_value
              ,(lead(value) over(order by time)- value) as val_diff
              ,(lead(time) over(order by time)- time) as time_diff
          from t
      )
select *
       ,generate_series- time as grp
       ,case when generate_series- time = 0 then
                  value
             else value + (val_diff*1.0/time_diff)*(generate_series-time)*1.0
         end*1.0 as val_grp
  from data
cross join generate_series(time, coalesce(next_time-1,time))


time
next_time
value
next_value
val_diff
time_diff
generate_series
grp
val_grp


1
5
3
5
2
4
1
0
3.0

1
5
3
5
2
4
2
1
3.5000000000000000000000

1
5
3
5
2
4
3
2
4.0000000000000000000000

1
5
3
5
2
4
4
3
4.5000000000000000000000

5
7
5
1
-4
2
5
0
5.0

5
7
5
1
-4
2
6
1
3.000000000000000000

7
9
1
8
7
2
7
0
1.0

7
9
1
8
7
2
8
1
4.500000000000000000

9
10
8
4
-4
1
9
0
8.0

10
null
4
null
null
null
10
0
4.0

time	next_time	value	next_value	val_diff	time_diff	generate_series	grp	val_grp
1	5	3	5	2	4	1	0	3.0
1	5	3	5	2	4	2	1	3.5000000000000000000000
1	5	3	5	2	4	3	2	4.0000000000000000000000
1	5	3	5	2	4	4	3	4.5000000000000000000000
5	7	5	1	-4	2	5	0	5.0
5	7	5	1	-4	2	6	1	3.000000000000000000
7	9	1	8	7	2	7	0	1.0
7	9	1	8	7	2	8	1	4.500000000000000000
9	10	8	4	-4	1	9	0	8.0
10	null	4	null	null	null	10	0	4.0