Пример использования LATERAL JOIN в PostgreSQL
31 января 2022
В СУБД для работы с временными рядами (time series DBMS) есть такое понятие, как ASOF JOIN. Хотя PostgreSQL и не поддерживает данный синтаксис, он позволяет выполнять те же запросы при помощи LATERAL JOIN’ов. Давайте разберемся, что это за JOIN’ы такие, и какую проблему они решают.
Рассмотрим такой сценарий. Есть два временных ряда. Для примера пусть будут температура и влажность:
ts TIMESTAMP NOT NULL,
city TEXT NOT NULL,
temperature INT NOT NULL);
CREATE TABLE humidity(
ts TIMESTAMP NOT NULL,
city TEXT NOT NULL,
humidity INT NOT NULL);
INSERT INTO temperature (ts, city, temperature)
SELECT ts + (INTERVAL '60 minutes' * random()), city, 30*random()
FROM generate_series('2022-01-01' :: TIMESTAMP,
'2022-01-31', '1 day') AS ts,
unnest(array['Moscow', 'Berlin']) AS city;
INSERT INTO humidity (ts, city, humidity)
SELECT ts + (INTERVAL '60 minutes' * random()), city, 100*random()
FROM generate_series('2022-01-01' :: TIMESTAMP,
'2022-01-31', '1 day') AS ts,
unnest(array['Moscow', 'Berlin']) AS city;
Временные ряды приезжают из разных источников и не обязательно синхронно, поэтому к временным меткам добавлен случайный разброс. Если теперь мы попытаемся сделать JOIN первой таблицы со второй по временной метке, то ничего не выйдет:
FROM temperature AS t
LEFT JOIN humidity AS h ON t.ts = h.ts;
Временные метки разные и сделать JOIN по их равенству невозможно.
Специально для таких случаев и придумали ASOF JOIN’ы. Их семантика следующая: берем данные из левой таблицы, а из правой — данные с такой же временной меткой или меньше. То есть, мы как бы спрашиваем «каким было последнее известное значение в правой таблице на такой-то момент времени».
PostgreSQL позволяет решить эту задачу, используя в запросе ключевое слово LATERAL. Подробнее о нем можно прочитать в документации. Вкратце, LATERAL можно использовать в SELECT- и JOIN-частях запроса. Ключевое слово позволяет ссылаться на строки, извлеченные из таблиц остальной частью запроса. Обычные SELECT и JOIN так не умеют.
Вот пример запроса:
FROM temperature AS t
LEFT JOIN LATERAL
( SELECT * FROM humidity
WHERE city = t.city AND ts <= t.ts
ORDER BY ts DESC LIMIT 1
) AS h ON TRUE
WHERE t.ts < '2022-01-05'
Попробуйте убрать ключевое слово LATERAL и посмотрите, что будет.
Убедимся, что запрос возвращает то, что нужно:
ts | city | temperature
----------------------------+--------+-------------
2022-01-01 00:15:56.237508 | Berlin | 6
2022-01-01 00:32:47.457148 | Moscow | 29
2022-01-02 00:19:26.726612 | Berlin | 2
2022-01-02 00:44:55.010613 | Moscow | 6
2022-01-03 00:30:12.831369 | Moscow | 6
2022-01-03 00:50:51.026596 | Berlin | 2
2022-01-04 00:03:29.004641 | Berlin | 15
2022-01-04 00:38:16.257926 | Moscow | 9
=# SELECT * FROM humidity WHERE ts < '2022-01-05' ORDER BY ts, city;
ts | city | humidity
----------------------------+--------+----------
2022-01-01 00:04:29.034182 | Moscow | 79
2022-01-01 00:04:50.904393 | Berlin | 36
2022-01-02 00:00:31.940381 | Berlin | 46
2022-01-02 00:12:16.295859 | Moscow | 18
2022-01-03 00:26:22.054815 | Moscow | 43
2022-01-03 00:53:20.204302 | Berlin | 66
2022-01-04 00:30:30.095735 | Moscow | 97
2022-01-04 00:35:36.344991 | Berlin | 66
=# SELECT ... приведенный выше запрос с LATERAL ...
ts | city | temperature | humidity
----------------------------+--------+-------------+----------
2022-01-01 00:15:56.237508 | Berlin | 6 | 36
2022-01-01 00:32:47.457148 | Moscow | 29 | 79
2022-01-02 00:19:26.726612 | Berlin | 2 | 46
2022-01-02 00:44:55.010613 | Moscow | 6 | 18
2022-01-03 00:30:12.831369 | Moscow | 6 | 43
2022-01-03 00:50:51.026596 | Berlin | 2 | 46 <-- !!!
2022-01-04 00:03:29.004641 | Berlin | 15 | 66
2022-01-04 00:38:16.257926 | Moscow | 9 | 97
Обратите внимание на третью строчку снизу. Влажность в Берлине была взята за второе января, а не за третье. Но если вы внимательно посмотрите на временные метки, то убедитесь, что результат верный.
И напоследок проверим, что такие запросы могут в индексы:
-- как-то правильнее, но и ускоряет запрос на ~13% по сравнению
-- с простым индексом.
CREATE INDEX humidity_idx ON humidity (ts, city) INCLUDE (humidity);
-- Нужно добавить побольше данных, чтобы СУБД предпочла использовать
-- индекс, а не сканировать закэшированные строки в памяти.
INSERT INTO temperature (ts, city, temperature)
SELECT ts + (INTERVAL '60 minutes' * random()), city, 30*random()
FROM generate_series('2010-01-01' :: TIMESTAMP,
'2020-12-31', '1 hour') AS ts,
unnest(array['Moscow', 'Berlin']) AS city;
INSERT INTO humidity (ts, city, humidity)
SELECT ts + (INTERVAL '60 minutes' * random()), city, 100*random()
FROM generate_series('2010-01-01' :: TIMESTAMP,
'2020-12-31', '1 hour') AS ts,
unnest(array['Moscow', 'Berlin']) AS city;
-- Смотрим EXPLAIN (или EXPLAIN ANALYZE).
EXPLAIN SELECT t.ts, t.city, t.temperature, h.humidity
FROM temperature AS t
LEFT JOIN LATERAL
( SELECT * FROM humidity
WHERE city = t.city AND ts <= t.ts
ORDER BY ts DESC LIMIT 1
) AS h ON TRUE;
Какой же план мы увидим? Что можно сказать об эффективности запроса? Ответы на эти вопросы я предлагаю получить вам самостоятельно, в качестве упражнения.
Метки: PostgreSQL, СУБД.
Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.