PostgreSQL - выборка строки, которая имеет значение Max для столбца

Ответ 1

В таблице с псевдослучайными строками 158k (usr_id равномерно распределены между 0 и 10k, trans_id равномерно распределены между 0 и 30),

По стоимости запроса, ниже, я имею в виду оценку затрат оптимизатора затрат Postgres (с значениями по умолчанию xxx_cost Postgres), которая является оценкой взвешенных функций требуемых ресурсов ввода-вывода и ЦП; вы можете получить это, активировав PgAdminIII и запустив "Query/Explain (F7)" в запросе с параметрами "Query/Explain", установленными в "Анализ"

• Запрос Quassnoy имеет оценку затрат 745k (!) и завершается через 1,3 секунды (с учетом составного индекса на (usr_id, trans_id, time_stamp))
• Запрос запроса имеет оценку стоимости 93 тыс. и завершается за 2,9 секунды (с учетом составного индекса на (usr_id, trans_id))
• Запрос № 1 ниже имеет оценку стоимости 16k и завершается в 800 мс (с учетом составного индекса на (usr_id, trans_id, time_stamp))
• Запрос № 2 ниже имеет оценку стоимости 14k и завершается в 800 мс (с учетом индекса составной функции на (usr_id, EXTRACT(EPOCH FROM time_stamp), trans_id))
  • это специфичный для Postgres
• Запрос № 3 ниже (Postgres 8.4+) имеет время сметы и время завершения, сравнимое с запросом # 2 (или лучше, чем) (с учетом составного индекса (usr_id, time_stamp, trans_id)); он имеет преимущество сканирования таблицы lives только один раз и, если вы временно увеличите (при необходимости) work_mem, чтобы разместить сортировку в памяти, это будет, безусловно, самый быстрый из всех запросов.

Все времена выше включают поиск полного набора результатов в 10 000 строк.

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

Лучшее время выполнения запроса достигается при работе в отдельной базе данных без нагрузки (например, при игре с pgAdminIII на ПК-разработчике). Время запроса будет варьироваться в зависимости от фактического распространения загрузки/распространения данных. Когда один запрос появляется немного быстрее (< 20%), чем другой, но имеет гораздо более высокую стоимость, обычно будет разумнее выбрать тот, у которого более высокое время выполнения, но более низкая стоимость.

Если вы ожидаете, что на вашем рабочем компьютере не будет конкуренции за память на момент выполнения запроса (например, кеш RDBMS и кеш файловой системы не будут разбиты на параллельные запросы и/или активность файловой системы), тогда запрос время, которое вы получили в автономном режиме (например, pgAdminIII на компьютере разработки), будет репрезентативным. Если в производственной системе есть конкуренция, время запроса будет ухудшаться пропорционально расчетному соотношению затрат, так как запрос с более низкой стоимостью не полагается так много на кеш, тогда как запрос с более высокой стоимостью снова и снова будет пересматривать одни и те же данные (запуск дополнительный ввод-вывод в отсутствие стабильного кеша), например:

              cost | time (dedicated machine) |     time (under load) |
-------------------+--------------------------+-----------------------+
some query A:   5k | (all data cached)  900ms | (less i/o)     1000ms |
some query B:  50k | (all data cached)  900ms | (lots of i/o) 10000ms |

Не забудьте запустить ANALYZE lives один раз после создания необходимых индексов.

ЗапроС# 1

-- incrementally narrow down the result set via inner joins
--  the CBO may elect to perform one full index scan combined
--  with cascading index lookups, or as hash aggregates terminated
--  by one nested index lookup into lives - on my machine
--  the latter query plan was selected given my memory settings and
--  histogram
SELECT
  l1.*
 FROM
  lives AS l1
 INNER JOIN (
    SELECT
      usr_id,
      MAX(time_stamp) AS time_stamp_max
     FROM
      lives
     GROUP BY
      usr_id
  ) AS l2
 ON
  l1.usr_id     = l2.usr_id AND
  l1.time_stamp = l2.time_stamp_max
 INNER JOIN (
    SELECT
      usr_id,
      time_stamp,
      MAX(trans_id) AS trans_max
     FROM
      lives
     GROUP BY
      usr_id, time_stamp
  ) AS l3
 ON
  l1.usr_id     = l3.usr_id AND
  l1.time_stamp = l3.time_stamp AND
  l1.trans_id   = l3.trans_max

Запрос № 2

-- cheat to obtain a max of the (time_stamp, trans_id) tuple in one pass
-- this results in a single table scan and one nested index lookup into lives,
--  by far the least I/O intensive operation even in case of great scarcity
--  of memory (least reliant on cache for the best performance)
SELECT
  l1.*
 FROM
  lives AS l1
 INNER JOIN (
   SELECT
     usr_id,
     MAX(ARRAY[EXTRACT(EPOCH FROM time_stamp),trans_id])
       AS compound_time_stamp
    FROM
     lives
    GROUP BY
     usr_id
  ) AS l2
ON
  l1.usr_id = l2.usr_id AND
  EXTRACT(EPOCH FROM l1.time_stamp) = l2.compound_time_stamp[1] AND
  l1.trans_id = l2.compound_time_stamp[2]

Обновление для 2013/01/29

Наконец, начиная с версии 8.4 Postgres поддерживает Функция окна, что означает, что вы можете написать что-то простое и эффективное, например:

Запрос № 3

-- use Window Functions
-- performs a SINGLE scan of the table
SELECT DISTINCT ON (usr_id)
  last_value(time_stamp) OVER wnd,
  last_value(lives_remaining) OVER wnd,
  usr_id,
  last_value(trans_id) OVER wnd
 FROM lives
 WINDOW wnd AS (
   PARTITION BY usr_id ORDER BY time_stamp, trans_id
   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 );

Ответ 2

Я бы предложил чистую версию на основе DISTINCT ON (см. docs):

SELECT DISTINCT ON (usr_id)
    time_stamp,
    lives_remaining,
    usr_id,
    trans_id
FROM lives
ORDER BY usr_id, time_stamp DESC, trans_id DESC;

Ответ 3

Здесь другой метод, который не использует коррелированные подзапросы или GROUP BY. Я не являюсь экспертом в настройке производительности PostgreSQL, поэтому предлагаю вам попробовать как это, так и решения, предоставленные другими людьми, чтобы узнать, какие из них лучше для вас.

SELECT l1.*
FROM lives l1 LEFT OUTER JOIN lives l2
  ON (l1.usr_id = l2.usr_id AND (l1.time_stamp < l2.time_stamp 
   OR (l1.time_stamp = l2.time_stamp AND l1.trans_id < l2.trans_id)))
WHERE l2.usr_id IS NULL
ORDER BY l1.usr_id;

Я предполагаю, что trans_id является уникальным по крайней мере по любому заданному значению time_stamp.

Ответ 4

Мне нравится стиль Ответ Майка Вудхауса на другой странице, о которой вы говорили. Это особенно красноречиво, когда максимизированная вещь - это всего лишь один столбец, и в этом случае подзапрос может просто использовать MAX(some_col) и GROUP BY другие столбцы, но в вашем случае у вас есть максимально возможное количество из 2 частей, вы все еще может сделать это, используя ORDER BY plus LIMIT 1 вместо этого (как сделано Quassnoi):

SELECT * 
FROM lives outer
WHERE (usr_id, time_stamp, trans_id) IN (
    SELECT usr_id, time_stamp, trans_id
    FROM lives sq
    WHERE sq.usr_id = outer.usr_id
    ORDER BY trans_id, time_stamp
    LIMIT 1
)

Я нахожу использование синтаксиса конструктора строк WHERE (a, b, c) IN (subquery) nice, потому что он сокращает количество необходимых слов.

Ответ 5

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

SELECT (array_agg(tree.id ORDER BY tree_size.size)))[1]
FROM tree JOIN forest ON (tree.forest = forest.id)
GROUP BY forest.id

Когда вы группируете деревья по лесам, будет несортированный список деревьев, и вам нужно найти самый большой. Первое, что вам нужно сделать, это отсортировать строки по их размерам и выбрать первый из списка. Это может показаться неэффективным, но если у вас есть миллионы строк, он будет намного быстрее, чем решения, в которых есть условия JOIN и WHERE.

Кстати, обратите внимание, что ORDER_BY для array_agg введен в Postgresql 9.0

Ответ 6

SELECT  l.*
FROM    (
        SELECT DISTINCT usr_id
        FROM   lives
        ) lo, lives l
WHERE   l.ctid = (
        SELECT ctid
        FROM   lives li
        WHERE  li.usr_id = lo.usr_id
        ORDER BY
          time_stamp DESC, trans_id DESC
        LIMIT 1
        )

Создание индекса на (usr_id, time_stamp, trans_id) значительно улучшит этот запрос.

Вы всегда должны всегда иметь PRIMARY KEY в своих таблицах.

Ответ 7

Я думаю, у вас здесь одна серьезная проблема: нет монотонно увеличивающегося "счетчика", чтобы гарантировать, что данная строка произошла позже во времени, чем другая. Возьмите этот пример:

timestamp   lives_remaining   user_id   trans_id
10:00       4                 3         5
10:00       5                 3         6
10:00       3                 3         1
10:00       2                 3         2

Вы не можете определить из этих данных, которые являются самой последней записью. Это второй или последний? Нет никакой функции sort или max(), которую вы можете применить к любому из этих данных, чтобы дать правильный ответ.

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

В качестве альтернативы, используйте trans_id, который не будет переворачиваться очень и очень долго. Имея trans_id, который перекатывается, вы не можете сказать (для той же временной метки), является ли trans_id 6 более поздним, чем trans_id 1, если вы не делаете сложную математику.