Оптимизировать запрос GROUP BY для получения последней записи для пользователя

Question

Оптимизировать запрос GROUP BY для получения последней записи для пользователя

У меня есть следующая таблица (упрощенная форма) в Postgres 9.2

CREATE TABLE user_msg_log (
    aggr_date DATE,
    user_id INTEGER,
    running_total INTEGER
);

Он содержит до одной записи на пользователя и в день. Будет около 500 тысяч записей в день в течение 300 дней. running_total всегда увеличивается для каждого пользователя.

Я хочу эффективно получить последнюю запись для каждого пользователя до определенной даты. Мой запрос:

SELECT user_id, max(aggr_date), max(running_total) 
FROM user_msg_log 
WHERE aggr_date <= :mydate 
GROUP BY user_id

что очень медленно. Я также попробовал:

SELECT DISTINCT ON(user_id), aggr_date, running_total
FROM user_msg_log
WHERE aggr_date <= :mydate
ORDER BY user_id, aggr_date DESC;

который имеет тот же план и одинаково медленно.

Пока у меня есть один индекс для user_msg_log(aggr_date), но он мало помогает. Есть ли какой-то другой индекс, который я должен использовать, чтобы ускорить это, или любой другой способ достичь того, чего я хочу?

88

sql postgresql indexing greatest-n-per-group postgresql-performance

Источник

user1140873 27 авг '14 в 20:31

3 ответа

Решение

Это не отдельный ответ, а комментарий к ответу@ Эрвина. Для примера 2a, бокового соединения, запрос может быть улучшен путем сортировки users таблица для использования локальности индекса на user_msg_log,

SELECT u.user_id, l.aggr_date, l.running_total
  FROM (SELECT user_id FROM users ORDER BY user_id) u,
       LATERAL (SELECT aggr_date, running_total
                  FROM user_msg_log
                 WHERE user_id = u.user_id -- lateral reference
                   AND aggr_date <= :mydate
              ORDER BY aggr_date DESC NULLS LAST
                 LIMIT 1) l;

Смысл в том, что поиск индекса стоит дорого, если user_id значения случайны. Разбираясь user_id Во-первых, последующее боковое соединение будет похоже на простое сканирование индекса user_msg_log, Хотя оба плана запросов выглядят одинаково, время выполнения может сильно отличаться, особенно для больших таблиц.

Стоимость сортировки минимальна, особенно если на user_id поле.

14

Источник

user2684232 25 мар '16 в 16:13

Возможно, поможет другой индекс в таблице. Попробуй это: user_msg_log(user_id, aggr_date), Я не уверен, что Postgres будет оптимально использовать с distinct on,

Итак, я бы придерживался этого индекса и попробовал эту версию:

select *
from user_msg_log uml
where not exists (select 1
                  from user_msg_log uml2
                  where uml2.user_id = u.user_id and
                        uml2.aggr_date <= :mydate and
                        uml2.aggr_date > uml.aggr_date
                 );

Это должно заменить сортировку / группировку поиском по индексу. Это может быть быстрее.

6

Источник

user1144035 27 авг '14 в 20:42

Другие вопросы по тегам sql postgresql indexing greatest-n-per-group postgresql-performance

user939860 27 авг '14 в 20:53 2014-08-27 20:53 · Accepted Answer · 2014-08-27 20:53

Для лучшей производительности чтения вам нужен многоколонный индекс:

CREATE INDEX user_msg_log_combo_idx
ON user_msg_log (user_id, aggr_date DESC NULLS LAST)

Чтобы сделать сканирование только по индексу, добавьте столбец, в котором нет необходимости running_total:

CREATE INDEX user_msg_log_combo_covering_idx
ON user_msg_log (user_id, aggr_date DESC NULLS LAST, running_total)

Зачем DESC NULLS LAST?

Неиспользуемый индекс в диапазоне дат запроса

Для нескольких строк в user_id или маленькие столики простые DISTINCT ON является одним из самых быстрых и простых решений:

Выберите первую строку в каждой группе GROUP BY?

Для многих строк в user_id Свободное сканирование индекса будет (намного) более эффективным. Это не реализовано в Postgres (по крайней мере, до Postgres 10), но есть способы его эмулировать:

1. Нет отдельной таблицы с уникальными пользователями

Следующие решения выходят за рамки того, что описано в Postgres Wiki.
^{С отдельным} users ^{Таблица 2. Решения в 2. ниже, как правило, проще и быстрее.}

1a. Рекурсивный CTE с `LATERAL` присоединиться

Стандартные табличные выражения требуют Postgres 8.4+.
LATERAL требуется Postgres 9.3+.

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT user_id, aggr_date, running_total
   FROM   user_msg_log
   WHERE  aggr_date <= :mydate
   ORDER  BY user_id, aggr_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT u.user_id, u.aggr_date, u.running_total
   FROM   cte c
   ,      LATERAL (
      SELECT user_id, aggr_date, running_total
      FROM   user_msg_log
      WHERE  user_id > c.user_id   -- lateral reference
      AND    aggr_date <= :mydate  -- repeat condition
      ORDER  BY user_id, aggr_date DESC NULLS LAST
      LIMIT  1
      ) u
   )
SELECT user_id, aggr_date, running_total
FROM   cte
ORDER  BY user_id;

Это предпочтительнее в текущих версиях Postgres, и легко получить произвольные столбцы. Больше объяснения в главе 2а. ниже.

1б. Рекурсивный CTE с коррелированными подзапросами

Удобно извлекать либо один столбец, либо всю строку. В примере используется весь тип строки таблицы. Возможны другие варианты.

WITH RECURSIVE cte AS (
   (
   SELECT u  -- whole row
   FROM   user_msg_log u
   WHERE  aggr_date <= :mydate
   ORDER  BY user_id, aggr_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT u1  -- again, whole row
           FROM   user_msg_log u1
           WHERE  user_id > (c.u).user_id  -- parentheses to access row type
           AND    aggr_date <= :mydate     -- repeat predicate
           ORDER  BY user_id, aggr_date DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  (c.u).user_id IS NOT NULL        -- any NOT NULL column of the row
   )
SELECT (u).*                               -- finally decompose row
FROM   cte
WHERE  (u).user_id IS NOT NULL             -- any column defined NOT NULL
ORDER  BY (u).user_id;

Это может вводить в заблуждение, чтобы проверить значение строки с c.u IS NOT NULL, Это только возвращает true если каждый столбец тестируемой строки NOT NULL и потерпит неудачу, если один NULL значение содержится. (У меня была эта ошибка в моем ответе в течение некоторого времени.) Вместо этого, чтобы утверждать, что строка была найдена в предыдущей итерации, протестируйте один столбец строки, которая определена NOT NULL (как первичный ключ). Больше:

Более подробное объяснение этого запроса в главе 2b. ниже.
Связанные ответы:

2. С отдельным `users` Таблица

Расположение таблицы вряд ли имеет значение, если у нас есть ровно по одной строке user_id, Пример:

CREATE TABLE users (
   user_id  serial PRIMARY KEY
 , username text NOT NULL
);

В идеале таблица физически отсортирована. Увидеть:

Оптимизировать диапазон запросов Postgres timestamp

Или он достаточно мал (низкая мощность), что вряд ли имеет значение.
В противном случае сортировка строк в запросе может помочь в дальнейшей оптимизации производительности. Смотрите дополнение Ган Ляна.

2а. `LATERAL` присоединиться

SELECT u.user_id, l.aggr_date, l.running_total
FROM   users u
CROSS  JOIN LATERAL (
   SELECT aggr_date, running_total
   FROM   user_msg_log
   WHERE  user_id = u.user_id  -- lateral reference
   AND    aggr_date <= :mydate
   ORDER  BY aggr_date DESC NULLS LAST
   LIMIT  1
   ) l;

JOIN LATERAL позволяет ссылаться на предыдущие FROM элементы на одном уровне запроса. Вы получаете один индекс (-только) для каждого пользователя.

В чем разница между LATERAL и подзапросом в PostgreSQL?

Рассмотрим возможное улучшение путем сортировки users Таблица, предложенная Gang Liang в другом ответе. Если физический порядок сортировки users таблица совпадает с индексом на user_msg_log тебе это не нужно.

Вы не получите результаты для пользователей, отсутствующих в users таблица, даже если у вас есть записи в user_msg_log, Как правило, вы должны иметь ограничение внешнего ключа, обеспечивающее ссылочную целостность, чтобы исключить это.

Вы также не получите строку для любого пользователя, который не имеет соответствующей записи в user_msg_log, Это соответствует вашему первоначальному вопросу. Если вам нужно включить эти строки в результат, используйте LEFT JOIN LATERAL ... ON true вместо CROSS JOIN LATERAL:

Вызывать функцию, возвращающую множество, с аргументом массива несколько раз

Эта форма также удобна для получения более чем одной строки (но не всех) на пользователя. Просто используйте LIMIT n вместо LIMIT 1,

По сути, все они будут делать то же самое:

JOIN LATERAL ... ON true
CROSS JOIN LATERAL ...
, LATERAL ...

Последний имеет более низкий приоритет, хотя. Явный JOIN связывает перед запятой.

2b. Коррелированный подзапрос

Хороший выбор для извлечения одного столбца из одной строки. Пример кода:

Оптимизировать групповой максимальный запрос

То же самое возможно для нескольких столбцов, но вам нужно больше умов:

CREATE TEMP TABLE combo (aggr_date date, running_total int);

SELECT user_id, (my_combo).*  -- note the parentheses
FROM (
   SELECT u.user_id
        , (SELECT (aggr_date, running_total)::combo
           FROM   user_msg_log
           WHERE  user_id = u.user_id
           AND    aggr_date <= :mydate
           ORDER  BY aggr_date DESC NULLS LAST
           LIMIT  1) AS my_combo
   FROM   users u
   ) sub;

подобно LEFT JOIN LATERAL выше, этот вариант включает в себя всех пользователей, даже без записей в user_msg_log, Ты получаешь NULL за my_combo, который вы можете легко отфильтровать с WHERE предложение во внешнем запросе, если это необходимо.
_{Nitpick: во внешнем запросе вы не можете различить, был ли подзапрос не найден ряд или все возвращаемые значения оказались NULL - тот же результат.} _{Вы должны будете включить} NOT NULL _{столбец в подзапросе, чтобы быть уверенным.}
Коррелированный подзапрос может возвращать только одно значение. Вы можете заключить несколько столбцов в составной тип. Но чтобы разложить его позже, Postgres требует хорошо известного составного типа. Анонимные записи могут быть разложены только с предоставлением списка определений столбцов.
Используйте зарегистрированный тип, такой как тип строки существующей таблицы, или создайте тип. Зарегистрируйте составной тип явно (и постоянно) с CREATE TYPE или создайте временную таблицу (автоматически удаляемую в конце сеанса) для временного предоставления типа строки. Приведение к этому типу: (aggr_date, running_total)::combo
Наконец, мы не хотим разлагать combo на том же уровне запроса. Из-за слабости в планировщике запросов это оценило бы подзапрос один раз для каждого столбца (до Postgres 9.6 - улучшения запланированы для Postgres 10). Вместо этого сделайте его подзапросом и разложите во внешнем запросе.

Связанные с:

Получить значения из первой и последней строки в группе

Демонстрация всех 4 запросов с записями в журнале 100k и пользователями 1k:
SQL Fiddle - стр 9.6
дБ <> скрипка здесь - стр. 10

Оптимизировать запрос GROUP BY для получения последней записи для пользователя

3 ответа

1. Нет отдельной таблицы с уникальными пользователями

1a. Рекурсивный CTE с LATERAL присоединиться

1б. Рекурсивный CTE с коррелированными подзапросами

2. С отдельным users Таблица

2а. LATERAL присоединиться

2b. Коррелированный подзапрос

1a. Рекурсивный CTE с `LATERAL` присоединиться

2. С отдельным `users` Таблица

2а. `LATERAL` присоединиться