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

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

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

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

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

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

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

если вы ожидаете, что во время выполнения запроса на вашей рабочей машине не будет конкуренции за память (например, кэш 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
 );