Запрос последних N связанных строк в строке

У меня есть следующий запрос, который выбирает id из последних N observations для каждого station:

SELECT id
FROM (
  SELECT station_id, id, created_at,
         row_number() OVER(PARTITION BY station_id
                           ORDER BY created_at DESC) AS rn
  FROM (
      SELECT station_id, id, created_at
      FROM observations
  ) s
) s
WHERE rn <= #{n}
ORDER BY station_id, created_at DESC;

У меня есть индексы на id, station_id, created_at,

Это единственное решение, которое я придумала, которое может получить более одной записи на станцию. Однако это довольно медленно (154,0 мс для таблицы из 81000 записей).

Как я могу ускорить запрос?

Источник

2 ответа

Решение

Предполагая текущую версию Postgres 9.3.

Индекс

Во-первых, поможет многоколонный индекс:

CREATE INDEX observations_special_idx
ON observations(station_id, created_at DESC, id)

created_at DESC немного лучше подходит, но индекс все равно будет сканироваться назад почти с той же скоростью без DESC,

Если предположить, created_at определено NOT NULL еще посмотрим DESC NULLS LAST в индексе и запросе:

Последний столбец id полезно только в том случае, если вы получаете сканирование только по индексу, что, вероятно, не сработает, если вы постоянно добавляете много новых строк. В этом случае удалите id из индекса.

Упрощенный запрос (все еще медленный)

Упростите ваш запрос, внутренний подвыбор не поможет:

SELECT id
FROM  (
  SELECT station_id, id, created_at
       , row_number() OVER (PARTITION BY station_id
                            ORDER BY created_at DESC) AS rn
  FROM   observations
  ) s
WHERE  rn <= #{n}
ORDER  BY station_id, created_at DESC;

Должно быть немного быстрее, но все еще медленно.

Быстрый запрос

  • Предполагая, что у вас относительно мало станций и относительно много наблюдений на станцию.
  • Также при условии station_id идентификатор определяется как NOT NULL,

Чтобы быть действительно быстрым, вам нужен эквивалент свободного сканирования индекса (не реализованный в Postgres). Соответствующий ответ:

Если у вас есть отдельная таблица stations (что кажется вероятным), вы можете подражать этому с JOIN LATERAL (Postgres 9.3+):

SELECT o.id
FROM   stations s
JOIN   LATERAL (
   SELECT id, created_at
   FROM   observations
   WHERE  station_id = s.id  -- lateral reference
   ORDER  BY created_at DESC
   LIMIT  #{n}
   ) o ON TRUE
ORDER  BY s.id, o.created_at DESC;

Если у вас нет таблицы stations следующая лучшая вещь должна была бы создать и поддержать тот. Возможно добавить ссылку на внешний ключ для обеспечения целостности отношений.

Если это не вариант, вы можете перегнать такой стол на лету. Простые варианты будут:

SELECT DISTINCT station_id FROM observations;
SELECT station_id FROM observations GROUP BY 1;

Но для этого потребуется последовательное сканирование и оно будет слишком медленным. Уловка Postgres в использовании вышеуказанного индекса (или любого индекса btree с station_id в качестве ведущего столбца) с рекурсивным CTE:

WITH RECURSIVE stations AS (
   (                  -- extra pair of parentheses ...
   SELECT station_id
   FROM   observations
   ORDER  BY station_id
   LIMIT  1
   )                  -- ... is required!
   UNION ALL
   SELECT (SELECT station_id
           FROM   observations
           WHERE  station_id > s.station_id
           ORDER  BY station_id
           LIMIT  1)
   FROM   stations s
   WHERE  s.station_id IS NOT NULL  -- serves as break condition
   )
SELECT station_id
FROM   stations
WHERE  station_id IS NOT NULL;      -- remove dangling row with NULL

Используйте это как замену для stations Таблица в приведенном выше простом запросе:

WITH RECURSIVE stations AS (
   (
   SELECT station_id
   FROM   observations
   ORDER  BY station_id
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT station_id
           FROM   observations
           WHERE  station_id > s.station_id
           ORDER  BY station_id
           LIMIT  1)
   FROM   stations s
   WHERE  s.station_id IS NOT NULL
   )
SELECT o.id
FROM   stations s
JOIN   LATERAL (
   SELECT id, created_at
   FROM   observations
   WHERE  station_id = s.station_id
   ORDER  BY created_at DESC
   LIMIT  #{n}
   ) o ON TRUE
WHERE  s.station_id IS NOT NULL
ORDER  BY s.station_id, o.created_at DESC;

Это все равно должно быть быстрее, чем у вас было на порядки.

SQL Fiddle.

Источник

Это хороший ответ, только если вы не обязаны запрашивать актуальные данные в реальном времени.

Подготовка (требуется postgresql 9.3)

drop materialized view test;
create materialized view test as select * from (
  SELECT station_id, id, created_at,
      row_number() OVER(
          PARTITION BY station_id
          ORDER BY created_at DESC
      ) as rn
  FROM (
      SELECT
          station_id,
          id,
          created_at
      FROM observations
  ) s
 ) q WHERE q.rn <= 100 -- use a value that will be your max limit number for further queries
ORDER BY station_id, rn DESC ;


create index idx_test on test(station_id,rn,created_at);

Как запросить данные:

select * from test where rn<10 order by station_id,created_at;

Ваш оригинальный запрос был 281 мс на моей машине, а этот новый был 15 мс.

Как обновить представление свежими данными:

refresh materialized view test;

У меня есть другое решение, которое не требует материализованного представления и работает с актуальными, актуальными данными. Но, учитывая, что вам не нужны последние данные, это материализованное представление гораздо более эффективно.

Источник
Другие вопросы по тегам