Sum with time gaps corrected, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

create table metrics (id int, run_hour timestamp, performance_hour timestamp, value int)\\

insert into metrics values (2, timestamp '2017-06-25 09:00:00', timestamp '2017-06-25 06:00:00', 6)\\
insert into metrics values (2, timestamp '2017-06-25 09:00:00', timestamp '2017-06-25 10:00:00', 5)\\
insert into metrics values (1, timestamp '2017-06-25 09:00:00', timestamp '2017-06-25 7:00:00', 6)\\
insert into metrics values (1, timestamp '2017-06-25 09:00:00', timestamp '2017-06-25 9:00:00', 5)\\

with recursive cte(id, run_hour, performance_hour, value) as
(
  select *
  from
  (
    select distinct on (id) 
      id, 
      run_hour,
      greatest(performance_hour, timestamp '2017-06-25 09:00') as performance_hour, 
      value
    from metrics
    where run_hour = timestamp '2017-06-25 09:00' 
      and performance_hour <= timestamp '2017-06-25 09:00'
    order by id, metrics.performance_hour desc
  ) start_by_id
  union all
  select 
    cte.id, 
    cte.run_hour,
    cte.performance_hour + interval '1 hour' as performance_hour,
    coalesce(m.value, cte.value) as value
  from cte
  left join metrics m on m.id = cte.id
                      and m.run_hour = cte.run_hour
                      and m.performance_hour = cte.performance_hour + interval '1 hour'
  where cte.performance_hour < timestamp '2017-06-25 12:00'
)
select id, sum(value)
from cte
group by id\\

drop table metrics\\


absolute service time: 0,48 sec