Postgres 11

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 temp table sample (dt timestamp, value real);
insert into sample values
     ('2019-01-20 00:29:43+00 ',0.29)
    ,('2019-01-20 00:35:06+00 ',0.31)
    ,('2019-01-20 00:35:50+00 ',0.41)
    ,('2019-01-20 00:36:32+00 ',0.26)
    ,('2019-01-20 00:37:20+00 ',0.33)
    ,('2019-01-20 00:41:30+00 ',0.42)
    ,('2019-01-20 00:42:28+00 ',0.35)
    ,('2019-01-20 00:43:14+00 ',0.52)
    ,('2019-01-20 00:44:18+00 ',0.25);

9 rows affected

CREATE OR REPLACE FUNCTION foo()
  RETURNS TABLE (dt timestamp
               , val real
               , sum_value real
               , time_at_sum timestamp
               , duration interval) AS
$func$
DECLARE
   _bound real := 1.0;          -- your bound here
   cur CURSOR FOR SELECT * FROM sample s ORDER BY s.dt; -- in chronological order
   s sample;                    -- cursor row 
BEGIN
   OPEN cur;
   FETCH cur INTO time_at_sum, sum_value; -- fetch first row into target

   FOR dt, val IN
      SELECT x.dt, x.value FROM sample x ORDER BY s.dt
   LOOP
      WHILE sum_value <= _bound LOOP
         FETCH cur INTO s;
         IF NOT FOUND THEN
            sum_value := NULL; time_at_sum := NULL;
            EXIT;
         END IF;
         sum_value := sum_value + s.value;
      END LOOP;
      IF sum_value > _bound THEN
         time_at_sum := s.dt;
      END IF;   
      duration := time_at_sum - dt;
      RETURN NEXT;
      sum_value := sum_value - val;
   END LOOP;
END
$func$  LANGUAGE plpgsql;
SELECT * FROM foo();


dt
val
sum_value
time_at_sum
duration


2019-01-20 00:29:43
0.29
1.01
2019-01-20 00:35:50
00:06:07

2019-01-20 00:35:06
0.31
1.31
2019-01-20 00:37:20
00:02:14

2019-01-20 00:35:50
0.41
1.42
2019-01-20 00:41:30
00:05:40

2019-01-20 00:36:32
0.26
1.01
2019-01-20 00:41:30
00:04:58

2019-01-20 00:37:20
0.33
1.1
2019-01-20 00:42:28
00:05:08

2019-01-20 00:41:30
0.42
1.29
2019-01-20 00:43:14
00:01:44

2019-01-20 00:42:28
0.35
1.12
2019-01-20 00:44:18
00:01:50

2019-01-20 00:43:14
0.52
null
null
null

2019-01-20 00:44:18
0.25
null
null
null


WITH tmp AS (
    SELECT *
        , sum(value) OVER (ORDER BY dt rows between current row and unbounded following) as forward_sum
    FROM sample
    ORDER BY dt)
SELECT t1.dt, t1.value
    , (t2.value + t1.forward_sum - t2.forward_sum) as "sum(value)"
    , t2.dt as "time_at_sum(value)_1" 
    , t2.dt - t1.dt as "Duration"
FROM tmp t1
LEFT JOIN LATERAL (
    SELECT * 
    FROM tmp t
    WHERE t1.forward_sum - t.forward_sum < 1
        AND (t.value + t1.forward_sum - t.forward_sum) >= 0.999
    ORDER BY dt DESC 
    LIMIT 1
    ) t2
ON TRUE


dt
value
sum(value)
time_at_sum(value)_1
Duration


2019-01-20 00:29:43
0.29
1.01
2019-01-20 00:35:50
00:06:07

2019-01-20 00:35:06
0.31
1.31
2019-01-20 00:37:20
00:02:14

2019-01-20 00:35:50
0.41
1
2019-01-20 00:37:20
00:01:30

2019-01-20 00:36:32
0.26
1.01
2019-01-20 00:41:30
00:04:58

2019-01-20 00:37:20
0.33
1.1
2019-01-20 00:42:28
00:05:08

2019-01-20 00:41:30
0.42
1.29
2019-01-20 00:43:14
00:01:44

2019-01-20 00:42:28
0.35
1.12
2019-01-20 00:44:18
00:01:50

2019-01-20 00:43:14
0.52
null
null
null

2019-01-20 00:44:18
0.25
null
null
null

dt	val	sum_value	time_at_sum	duration
2019-01-20 00:29:43	0.29	1.01	2019-01-20 00:35:50	00:06:07
2019-01-20 00:35:06	0.31	1.31	2019-01-20 00:37:20	00:02:14
2019-01-20 00:35:50	0.41	1.42	2019-01-20 00:41:30	00:05:40
2019-01-20 00:36:32	0.26	1.01	2019-01-20 00:41:30	00:04:58
2019-01-20 00:37:20	0.33	1.1	2019-01-20 00:42:28	00:05:08
2019-01-20 00:41:30	0.42	1.29	2019-01-20 00:43:14	00:01:44
2019-01-20 00:42:28	0.35	1.12	2019-01-20 00:44:18	00:01:50
2019-01-20 00:43:14	0.52	null	null	null
2019-01-20 00:44:18	0.25	null	null	null