Подсчет разницы секунд в таблице Postgres при self join

Question

есть таблица на 3 млн записей, делаю запрос к таблице с объединением, для поиска где разница между записями 20 секунд

SELECT a.value AS eventtype
    FROM logdata AS a 
    JOIN logdata AS b on
    abs(EXTRACT(EPOCH FROM (a.eventtime- b.eventtime))) < 20

создан индекс

CREATE INDEX epoch_idx ON logdata (extract(EPOCH FROM eventtime));

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

Answer 1

Я бы предложил по другому соединить 2 таблицы. Так как вас интересует записи у которых разница в 20 секунд. То логично что достаточно чтоб они шли друг за другом.

SELECT 
  a.value AS eventtype
FROM logdata AS a 
  JOIN logdata AS b 
   on a.id = b.id + 1
WHERE
  EXTRACT(EPOCH FROM (b.eventtime - a.eventtime)) < 20

На случай записи данных из разных источников с различным времени , предлагаю предварительно отсортировать и пронумеровать записи по eventtime с row_number, и далее по той же схеме.

Answer 2

Индекс по eventtime хорошо подойдет для сортировки, но в JOIN он не даст приемуществ. JOIN в этой задаче можно заменить на LAG или first_value (с установкой окна). Тогда сложность запроса становится линейной. Пропробуйте что-то вроде:

SELECT value FROM 
    (SELECT value, eventtime from logdata ORDER BY eventtime )
    WHERE EXTRACT(EPOCH FROM (eventtime - LAG(eventtime))) < 20

БЛОГ НА HUSL

Подсчет разницы секунд в таблице Postgres при self join

Ответы (2 шт):