Postgres 10

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


create table measurement_timestamps (id int,   start_time date,    stop_time date);
 insert into measurement_timestamps  values(1,    '2020-02-22',    '2020-02-24');
 insert into measurement_timestamps  values( 2,    '2020-02-25',    '2020-02-27'); 
1 rows affected

1 rows affected

create table sensor_double_precision(id int,   sensor_name varchar,    value_cal int,    timestamp date);
 insert into sensor_double_precision values(1,    'start_freq',            15,    '2020-02-23');
 insert into sensor_double_precision values(2,    'stop_freq',             18,    '2020-02-23');
 insert into sensor_double_precision values(3,    'start_freq',            15,    '2020-02-26');
 insert into sensor_double_precision values(4,    'stop_freq',             18,    '2020-02-26');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

with current_data as (
        select distinct on (mt.id)
                mt.id, sdp.sensor_name, sdp.value_cal
            from measurement_timestamps mt, sensor_double_precision sdp
            order by mt.id desc
    ),
    ids as (
        select distinct id from current_data
    ),
    sensor_names as (
        select distinct sensor_name from current_data
    )
    select ids.id, sensor_names.sensor_name, current_data.value_cal
    from ids cross join sensor_names
    left join current_data on (ids.id=current_data.id and sensor_names.sensor_name=current_data.sensor_name)
    order by ids.id,sensor_names.sensor_name


id
sensor_name
value_cal


1
start_freq
15

1
stop_freq
null

2
start_freq
null

2
stop_freq
18


select * from crosstab('select 
m.id, 
s.timestamp,
s.sensor_name,
s.value_cal
from measurement_timestamps m 
inner join sensor_double_precision s on s.timestamp between m.start_time and m.stop_time',
'select ''start_freq'' union select ''stop_freq'' ') as (id int, timestamp date, start_freq varchar, stop_freq varchar)



id
timestamp
start_freq
stop_freq


1
2020-02-23
15
18

2
2020-02-26
15
18


select 
m.id, 
min(s.value_cal) filter (where sensor_name='start_freq'),
min(s.value_cal) filter (where sensor_name='stop_freq'),
max(s.timestamp) filter (where sensor_name='stop_freq')
from measurement_timestamps m 
inner join sensor_double_precision s on s.timestamp between m.start_time and m.stop_time
group by m.id


id
min
min
max


2
15
18
2020-02-26

1
15
18
2020-02-23