Пример использования LATERAL JOIN в PostgreSQL

31 января 2022

В СУБД для работы с временными рядами (time series DBMS) есть такое понятие, как ASOF JOIN. Хотя PostgreSQL и не поддерживает данный синтаксис, он позволяет выполнять те же запросы при помощи LATERAL JOIN’ов. Давайте разберемся, что это за JOIN’ы такие, и какую проблему они решают.

Рассмотрим такой сценарий. Есть два временных ряда. Для примера пусть будут температура и влажность:

CREATE TABLE temperature(
  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 первой таблицы со второй по временной метке, то ничего не выйдет:

SELECT t.ts, t.city, t.temperature, h.humidity
FROM temperature AS t
LEFT JOIN humidity AS h ON t.ts = h.ts;

Временные метки разные и сделать JOIN по их равенству невозможно.

Специально для таких случаев и придумали ASOF JOIN’ы. Их семантика следующая: берем данные из левой таблицы, а из правой – данные с такой же временной меткой или меньше. То есть, мы как бы спрашиваем «каким было последнее известное значение в правой таблице на такой-то момент времени».

PostgreSQL позволяет решить эту задачу, используя в запросе ключевое слово LATERAL. Подробнее о нем можно прочитать в документации. Вкратце, LATERAL можно использовать в SELECT- и JOIN-частях запроса. Ключевое слово позволяет ссылаться на строки, извлеченные из таблиц остальной частью запроса. Обычные SELECT и JOIN так не умеют.

Вот пример запроса:

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
WHERE t.ts < '2022-01-05'

Попробуйте убрать ключевое слово LATERAL и посмотрите, что будет.

Убедимся, что запрос возвращает то, что нужно:

=# SELECT * FROM temperature WHERE ts < '2022-01-05' ORDER BY ts, city;
             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

Обратите внимание на третью строчку снизу. Влажность в Берлине была взята за второе января, а не за третье. Но если вы внимательно посмотрите на временные метки, то убедитесь, что результат верный.

И напоследок проверим, что такие запросы могут в индексы:

-- Создадим индекс. В этом случае построить covering индекс не только
-- как-то правильнее, но и ускоряет запрос на ~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;

Какой же план мы увидим? Что можно сказать об эффективности запроса? Ответы на эти вопросы я предлагаю получить вам самостоятельно, в качестве упражнения.