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 measurement (
    id serial,
    sensor_id integer,
    timestamp timestamp,
    value integer)
;
insert into measurement (sensor_id, timestamp, value)
values
(1, '2020-08-16 12:00:00', 5),
(2, '2020-08-16 12:01:00', 6),
(1, '2020-08-16 12:02:00', 4),
(2, '2020-08-16 12:02:00', 7),
(2, '2020-08-16 12:03:00', 3),
(1, '2020-08-16 12:05:00', 3),
(2, '2020-08-16 12:06:00', 4),
(2, '2020-08-16 12:07:00', 5),
(1, '2020-08-16 12:08:00', 6)
;
9 rows affected

select *
from measurement
order by timestamp, sensor_id


id
sensor_id
timestamp
value


1
1
2020-08-16 12:00:00
5

2
2
2020-08-16 12:01:00
6

3
1
2020-08-16 12:02:00
4

4
2
2020-08-16 12:02:00
7

5
2
2020-08-16 12:03:00
3

6
1
2020-08-16 12:05:00
3

7
2
2020-08-16 12:06:00
4

8
2
2020-08-16 12:07:00
5

9
1
2020-08-16 12:08:00
6


select  t.timestamp,
        max(m.value) filter (where s.sensor_id = 1) as value_1,
        max(m.value) filter (where s.sensor_id = 2) as value_2,
        abs(max(m.value) filter (where s.sensor_id = 2) -
            max(m.value) filter (where s.sensor_id = 1)
           ) as diff
from (values (1), (2)) s(sensor_id) cross join
     (select distinct timestamp
      from measurement
      where sensor_id in (1, 2)
     ) t left join lateral
     (select m.value
      from measurement m 
      where m.sensor_id = s.sensor_id and
            m.timestamp <= t.timestamp
      order by m.timestamp desc
      limit 1 
     ) m
     on 1=1
group by timestamp


timestamp
value_1
value_2
diff


2020-08-16 12:00:00
5
null
null

2020-08-16 12:01:00
5
6
1

2020-08-16 12:02:00
4
7
3

2020-08-16 12:03:00
4
3
1

2020-08-16 12:05:00
3
3
0

2020-08-16 12:06:00
3
4
1

2020-08-16 12:07:00
3
5
2

2020-08-16 12:08:00
6
5
1


with t as (
      select  t.timestamp,
              max(m.value) filter (where s.sensor_id = 1) as value_1,
              max(m.value) filter (where s.sensor_id = 2) as value_2,
              abs(max(m.value) filter (where s.sensor_id = 2) -
                  max(m.value) filter (where s.sensor_id = 1)
                 ) as diff
      from (values (1), (2)) s(sensor_id) cross join
           (select distinct timestamp
            from measurement
            where sensor_id in (1, 2)
           ) t left join lateral
           (select m.value
            from measurement m 
            where m.sensor_id = s.sensor_id and
                  m.timestamp <= t.timestamp
            order by m.timestamp desc
            limit 1 
           ) m
           on 1=1
      group by timestamp
     )
select *
from (select t.*,
             lag(diff) over (order by timestamp) as prev_diff,
             lead(diff) over (order by timestamp) as next_diff
      from t
     ) t
where (diff < prev_diff or prev_diff is null) and
      (diff < next_diff or next_diff is null)


timestamp
value_1
value_2
diff
prev_diff
next_diff


2020-08-16 12:01:00
5
6
1
null
3

2020-08-16 12:05:00
3
3
0
1
1

2020-08-16 12:08:00
6
5
1
2
null


with t as (
      select  t.timestamp,
              max(m.value) filter (where s.sensor_id = 1) as value_1,
              max(m.value) filter (where s.sensor_id = 2) as value_2,
              abs(max(m.value) filter (where s.sensor_id = 2) -
                  max(m.value) filter (where s.sensor_id = 1)
                 ) as diff
      from (values (1), (2)) s(sensor_id) cross join
           (select distinct timestamp
            from measurement
            where sensor_id in (1, 2)
           ) t left join lateral
           (select m.value
            from measurement m 
            where m.sensor_id = s.sensor_id and
                  m.timestamp <= t.timestamp
            order by m.timestamp desc
            limit 1 
           ) m
           on 1=1
      group by timestamp
     )
select *
from (select t.*,
             lag(diff) over (order by timestamp) as prev_diff,
             lead(diff) over (order by timestamp) as next_diff
      from (select t.*, lag(diff) over (order by timestamp) as test_for_dup
            from t
           ) t
      where test_for_dup is distinct from diff
     ) t
where (diff < prev_diff or prev_diff is null) and
      (diff < next_diff or next_diff is null)


timestamp
value_1
value_2
diff
test_for_dup
prev_diff
next_diff


2020-08-16 12:01:00
5
6
1
null
null
3

2020-08-16 12:05:00
3
3
0
1
1
1

2020-08-16 12:08:00
6
5
1
2
2
null

id	sensor_id	timestamp	value
1	1	2020-08-16 12:00:00	5
2	2	2020-08-16 12:01:00	6
3	1	2020-08-16 12:02:00	4
4	2	2020-08-16 12:02:00	7
5	2	2020-08-16 12:03:00	3
6	1	2020-08-16 12:05:00	3
7	2	2020-08-16 12:06:00	4
8	2	2020-08-16 12:07:00	5
9	1	2020-08-16 12:08:00	6

timestamp	value_1	value_2	diff
2020-08-16 12:00:00	5	null	null
2020-08-16 12:01:00	5	6	1
2020-08-16 12:02:00	4	7	3
2020-08-16 12:03:00	4	3	1
2020-08-16 12:05:00	3	3	0
2020-08-16 12:06:00	3	4	1
2020-08-16 12:07:00	3	5	2
2020-08-16 12:08:00	6	5	1