Как получить первый и последний элемент для каждого устройства?

Вы можете использовать row_number для присвоения возрастающего номера каждой строке с одним и тем же device_id. Если вы сделаете это дважды, один раз в порядке возрастания и один раз в порядке убывания, вы сможете захватить первую и последнюю строку для каждой группы:

select  device_id
,       created_at
,       value
from    (
        select  row_number() over (partition by device_id order by created_at) rn1
        ,       row_number() over (partition by device_id order by created_at desc) rn2
        ,       *
        from    interval_data
        ) i
where   device_id in (1, 3, 4)
        and (rn1 = 1 or rn2 = 1) -- First or last row per device
        and created_at between '2018-01-01 00:00:00' and '2019-01-01 00:00:00' 

Пример на SQL Fiddle.

Наиболее эффективный запрос зависит от деталей вашей настройки. Вы можете использовать существующую таблицу device и указать множество устройств и отобразить множество данных об интервалах для каждого устройства. Поэтому обычно запрос с двумя подзапросами LATERAL должен быть самым быстрым:

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;

Postgres может преобразовать это в план запроса только с быстрым сканированием индекса в большой таблице interval_data.

О LATERAL:

• В чем разница между LATERAL а подзапрос в PostgreSQL?

Убедитесь, что у вас есть индекс для interval_data(device_id, created_at). Если вам нужен только ограниченный набор столбцов в результате, может оказаться целесообразным добавить больше столбцов к этому индексу, чтобы получить из него сканирование только для индекса.

LEFT JOIN ... ON true сохраняет устройства без данных интервала в результате.

Чтобы ограничиться заданным набором идентификаторов устройств, добавьте к запросу:

...
WHERE  d.id IN (1000022, 1000023, 1000024);

И иметь индекс на device(id) - что в любом случае было бы типичным случаем.

Предположим, что у вас текущая версия Postgres и такая настройка:

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

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)
);

Если некоторые задействованные столбцы не определены NOT NULL, возможно, вам придется изменить детали.

Ограничение FK является необязательным для этого решения.

Подробное объяснение и обсуждение альтернатив:

• Выбрать первую строку в каждой группе GROUP BY?
• Оптимизировать запрос GROUP BY для получения последней записи на пользователя
• PostgreSQL: текущее количество строк для запрос 'по минутам'

Альтернатива для небольшого набора заданных идентификаторов устройств

Если вам удобно оконные функции, использующие настраиваемые оконные рамы, этот вариант не требует дополнительной таблицы device и может быть быстрее для небольшого набора идентификаторов:

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 (1000022, 1000023, 1000024)
WINDOW w AS (PARTITION BY device_id ORDER BY created_at
             RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);

То же, что и в первом запросе выше:

• Нет результатов для переданных идентификаторов устройств, которые не существуют.

Но отличается от первого запроса выше:

• Нет результатов для переданных идентификаторов устройств, которые действительно существуют, но без каких-либо данных об интервалах.

Про оконные рамы:

• Запрос PostgreSQL с максимальной и минимальной датой плюс связанный идентификатор для каждой строки
• Как использовать кольцевую структуру данных в оконные функции

db‹>скрипка здесь