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 sensor_groups (
id serial not null primary key,
description text not null
);
create table measurements (
id bigserial not null primary key,
sensor_group_id int not null,
ts timestamptz not null default current_timestamp
);
create table measurement_values (
id bigserial not null primary key,
measurement_id bigserial not null references measurements(id),
tp int not null,
value numeric not null,
unique (measurement_id, tp)
);
insert into sensor_groups (description) values
('Group 1'),
('Group 2');
insert into measurements (sensor_group_id, ts) values
(1, '2021-07-21T00:20:00+00:00'),
(1, '2021-07-15T00:20:00+00:00'),
(2, '2021-07-17T00:20:00+00:00'),
(1, '2021-07-13T00:20:00+00:00'),
(2, '2021-07-10T00:20:00+00:00');
insert into measurement_values (measurement_id, tp, value) values
(1, 1, 15),
(2, 2, 23),
(3, 2, 11),
(4, 1, 9),
(4, 2, 4),
2 rows affected
5 rows affected
7 rows affected
select *
from measurements as ms join measurement_values as mv on (ms.id = mv.measurement_id);
| id | sensor_group_id | ts | id | measurement_id | tp | value |
|---|---|---|---|---|---|---|
| 1 | 1 | 2021-07-21 01:20:00+01 | 1 | 1 | 1 | 15 |
| 2 | 1 | 2021-07-15 01:20:00+01 | 2 | 2 | 2 | 23 |
| 3 | 2 | 2021-07-17 01:20:00+01 | 3 | 3 | 2 | 11 |
| 4 | 1 | 2021-07-13 01:20:00+01 | 4 | 4 | 1 | 9 |
| 4 | 1 | 2021-07-13 01:20:00+01 | 5 | 4 | 2 | 4 |
| 5 | 2 | 2021-07-10 01:20:00+01 | 6 | 5 | 1 | 99 |
| 5 | 2 | 2021-07-10 01:20:00+01 | 7 | 5 | 2 | 36 |
select
*,
(select json_object_agg(mv.tp, mv.value) from measurement_values as mv where ms.id = mv.measurement_id) as vals
from measurements as ms;
| id | sensor_group_id | ts | vals |
|---|---|---|---|
| 1 | 1 | 2021-07-21 01:20:00+01 | { "1" : 15 } |
| 2 | 1 | 2021-07-15 01:20:00+01 | { "2" : 23 } |
| 3 | 2 | 2021-07-17 01:20:00+01 | { "2" : 11 } |
| 4 | 1 | 2021-07-13 01:20:00+01 | { "1" : 9, "2" : 4 } |
| 5 | 2 | 2021-07-10 01:20:00+01 | { "1" : 99, "2" : 36 } |
select distinct on (ms.sensor_group_id, mv.tp)
*
from measurements as ms join measurement_values as mv on (ms.id = mv.measurement_id)
order by ms.sensor_group_id, mv.tp, ms.ts desc;
| id | sensor_group_id | ts | id | measurement_id | tp | value |
|---|---|---|---|---|---|---|
| 1 | 1 | 2021-07-21 01:20:00+01 | 1 | 1 | 1 | 15 |
| 2 | 1 | 2021-07-15 01:20:00+01 | 2 | 2 | 2 | 23 |
| 5 | 2 | 2021-07-10 01:20:00+01 | 6 | 5 | 1 | 99 |
| 3 | 2 | 2021-07-17 01:20:00+01 | 3 | 3 | 2 | 11 |
with last_vals as (
select distinct on (ms.sensor_group_id, mv.tp) ms.sensor_group_id, ms.ts, mv.tp, mv.value
from measurements as ms join measurement_values as mv on (ms.id = mv.measurement_id)
order by ms.sensor_group_id, mv.tp, ms.ts desc)
select
sensor_group_id,
json_object_agg(tp, json_build_object('ts', ts, 'value', value)) as vals
from last_vals
group by sensor_group_id;
| sensor_group_id | vals |
|---|---|
| 1 | { "1" : {"ts" : "2021-07-21T01:20:00+01:00", "value" : 15}, "2" : {"ts" : "2021-07-15T01:20:00+01:00", "value" : 23} } |
| 2 | { "1" : {"ts" : "2021-07-10T01:20:00+01:00", "value" : 99}, "2" : {"ts" : "2021-07-17T01:20:00+01:00", "value" : 11} } |