Как с помощью TimescaleDB реализовать выгрузку последних данных с разбивкой по устройствам?

Question

[tttttv]

На проекте используем TimescaleDB, чтобы хранить координаты с GPS-трекеров. Есть задача - выгружать последние координаты по нескольким устройствам. Сейчас делаем запросом

SELECT distinct on (imei) * FROM locations WHERE imei IN (" + imeis_list + ") order by imei, dt desc

Возникла проблема - при выгрузке последней записи по нескольким imei (айди устройства), запрос не выполняется, долго грузит ответ

SELECT distinct on (imei) * FROM locations WHERE imei IN (867232054978268, 867232054981213, 867232054981015, 867232054980538, 867232054980157, 867232054977989, 867232054978367, 867232054978003, 867232054980769, 867232054981296, 867232054981411, 867232054978102, 867232054978235, 867232054977864, 867232054978664, 867232054980173, 867232054981544, 867232054980835, 867232054978474, 867232054976544, 867232054980876) order by imei, dt desc;

Но если запросить те же координаты тремя разными запросами (в каждом только часть имеев), то запрос выполняется практически моментально.

Вот как задается таблица в самом TimescaleDB:

CREATE TABLE locations (id SERIAL PRIMARY KEY, imei BIGINT, dt TIMESTAMP, lat REAL, long REAL, las BOOL, los BOOL, velocity INT, course INT, data VARBIT(128));
SELECT create_hypertable('locations','dt');
CREATE INDEX ix_imei_time ON locations (imei, dt DESC);

Где можем ошибаться? Сейчас в таблице 4.5M записей.

Comments

[Модератор]

Не надо ставить как можно больше тэгов. Лучше оставить один, но конкретный, с которым проблема.
См.п.3.1 Регламента. Также обратите внимание на п.3.4

[Дмитрий]

А чего говорит explain analyze на обоих запросах?

Answer 1

[mayton2019]

А попробуй убери из индекса дату.

CREATE INDEX ix_imei_time ON locations (imei);

Я не работал с TimescaleDB но вообще работать со вторичными индексами в time-series - это плохая тема.
Лучше выбрать лидирующий партишен как дата. И вторичный партишен как хеш от какого-то интересующего
атрибута или группы атрибутов. И тогда оптимизатор пойдет просто в тот фасет который соотвествует нужному
пересечению партишенов.

Comments

[tttttv]

С индексом без дат начало выгружать, но запрос вида

SELECT distinct on (imei) * FROM locations WHERE imei IN (" + imeis_list + ") order by imei

То есть без ,dt desc. Очень важна выгрузка с сортировкой по дате, есть какая-то альтернатива вторичному индексу?

[mayton2019]

21 imei сможешь отсортировать на клиенте?

Оптимизация - это всегда компромиссы.