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?.

CREATE TABLE device (
   id     serial PRIMARY KEY
 , device text NOT NULL
);

INSERT INTO device VALUES
   (206, 'dev206')
 , (207, 'dev207')
 , (208, 'dev208')  -- with no interval data
 ;

CREATE TABLE interval_data (
   id         serial PRIMARY KEY
 , device_id  int NOT NULL
 , created_at timestamp NOT NULL
 , value      numeric NOT NULL
 , CONSTRAINT device_fkey FOREIGN KEY (device_id) REFERENCES device (id)
);
    
INSERT INTO interval_data VALUES
   (15269665, 206, '2018-07-21 00:10:00', 5099.55)
 , (15270533, 206, '2018-07-21 00:20:00', 5099.61)
 , (15271400, 206, '2018-07-21 00:30:00', 5099.76)
 , (15272269, 206, '2018-07-21 00:40:00', 5099.85)
 , (15273132, 206, '2018-07-21 00:50:00', 5099.91)
 , (15274040, 206, '2018-07-21 01:00:00', 5099.97)
 , (15274909, 206, '2018-07-21 01:10:00', 5100.03)
 , (15275761, 206, '2018-07-21 01:20:00', 5100.11)
 , (15276629, 206, '2018-07-21 01:30:00', 5100.16)
 , (15277527, 206, '2018-07-21 01:40:00', 5100.34)
 , (15278351, 206, '2018-07-21 01:50:00', 5100.40)
 , (15279219, 206, '2018-07-21 02:00:00', 5100.45)
 , (15280085, 206, '2018-07-21 02:10:00', 5100.53)
 , (15280954, 206, '2018-07-21 02:20:00', 5100.59)
 , (15281858, 206, '2018-07-21 02:30:00', 5100.64)
 , (15282724, 206, '2018-07-21 02:40:00', 5100.75)
 , (15283627, 206, '2018-07-21 02:50:00', 5100.87) 
 , (15284495, 206, '2018-07-21 03:00:00', 5100.93)

 , (15290954, 207, '2018-07-22 02:20:00', 5100.59)  -- device 207
 , (15291858, 207, '2018-07-22 12:30:00', 5100.64)
 , (15292724, 207, '2018-07-22 22:40:00', 5100.75)
 ;
3 rows affected

21 rows affected

SELECT *  -- or just the columns you need
FROM   device d
LEFT   JOIN LATERAL (
   SELECT id AS first_intv_id, created_at AS first_created_at, value AS first_value
   FROM   interval_data
   WHERE  device_id = d.id
   ORDER  BY created_at
   LIMIT 1
   ) f ON true
LEFT   JOIN LATERAL (
   SELECT id AS last_intv_id, created_at AS last_created_at, value AS last_value
   FROM   interval_data
   WHERE  device_id = d.id
   ORDER  BY created_at DESC  -- NULLS LAST if column isn't NOT NULL
   LIMIT 1
   ) l ON true


id
device
first_intv_id
first_created_at
first_value
last_intv_id
last_created_at
last_value


206
dev206
15269665
2018-07-21 00:10:00
5099.55
15284495
2018-07-21 03:00:00
5100.93

207
dev207
15290954
2018-07-22 02:20:00
5100.59
15292724
2018-07-22 22:40:00
5100.75

208
dev208
null
null
null
null
null
null


SELECT DISTINCT ON (device_id)
       device_id
     , first_value(created_at) OVER w AS first_created_at
     , first_value(value)      OVER w AS first_value
     , last_value (created_at) OVER w AS last_created_at
     , last_value (value)      OVER w AS last_value
FROM   interval_data
WHERE  device_id IN (206, 207, 208)
WINDOW w AS (PARTITION BY device_id ORDER BY created_at
             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);


device_id
first_created_at
first_value
last_created_at
last_value


206
2018-07-21 00:10:00
5099.55
2018-07-21 03:00:00
5100.93

207
2018-07-22 02:20:00
5100.59
2018-07-22 22:40:00
5100.75

id	device	first_intv_id	first_created_at	first_value	last_intv_id	last_created_at	last_value
206	dev206	15269665	2018-07-21 00:10:00	5099.55	15284495	2018-07-21 03:00:00	5100.93
207	dev207	15290954	2018-07-22 02:20:00	5100.59	15292724	2018-07-22 22:40:00	5100.75
208	dev208	null	null	null	null	null	null