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

В таблице с 158 000 псевдослучайных query-optimization строк (usr_id равномерно postgres распределен между 0 и 10 sql-query 000, trans_id равномерно распределен pgsql между 0 и 30)

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

• Стоимость запроса Quassnoy составляет 745 тыс. (!), и он выполняется за 1,3 секунды (с учетом составного индекса на (usr_id, trans_id, time_stamp))
• Запрос Билла оценивается в 93 тыс. и выполняется за 2,9 секунды (с учетом составного индекса на (usr_id, trans_id))
• Запрос №1 ниже имеет оценку стоимости 16 КБ и выполняется за 800 мс (с учетом составного индекса на (usr_id, trans_id, time_stamp))
• Запрос № 2 ниже имеет оценку 14 КБ и выполняется за 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 для размещения сортировки в памяти, это будет самый быстрый из всех запросов.

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

Ваша цель sql-tuning - минимальная смета и минимальное cbo время выполнения запроса query-performance с упором на ориентировочную query-tuning стоимость. Выполнение запроса cost-based-optimizer может в значительной степени cost-based-optimizer зависеть от условий выполнения sql (например, полностью ли кэшированы sql в памяти соответствующие query-optimization строки или нет), в то время postgres как оценка стоимости не зависит. С pgsql другой стороны, имейте в sql-select виду, что смета - это всего postgres лишь приблизительная оценка.

Наилучшее cost-based-optimizer время выполнения запроса pgsql достигается при работе с pgsql выделенной базой данных без pgsql нагрузки (например, игра postgresql с pgAdminIII на ПК для разработки). Время sql выполнения запроса будет postgresql варьироваться в производственной sql-select среде в зависимости от фактической cost-based-optimizer нагрузки на машину / распределения postgresql доступа к данным. Когда один sql-postgres запрос выполняется немного sql быстрее (<20%), чем другой, но sql-select имеет намного более высокую стоимость, обычно query-tuning разумнее выбрать тот, у которого cost-based-optimizer время выполнения выше, но query-tuning ниже стоимость.

Если вы ожидаете, что cbo во время выполнения запроса pgsql не будет конкуренции за память sql на вашем производственном sql-tuning компьютере (например, кеш postgresql реляционной СУБД и кеш файловой postgres системы не будут перегружены cost-based-optimizer параллельными запросами и cost-based-optimizer / или активностью файловой sql-tuning системы), тогда запрос время, полученное pgsql вами в автономном режиме sql-tuning (например, pgAdminIII на cbo ПК для разработки), будет postgresql репрезентативным. Если в query-optimization производственной системе query-performance возникает конкуренция, время sql-query запроса будет уменьшаться query-optimization пропорционально оценочному sql-syntax соотношению затрат, поскольку sql-select запрос с более низкой стоимостью sql-tuning не так сильно зависит от query-tuning кеша , тогда как запрос с более высокой sql-tuning стоимостью будет пересматривать pgsql одни и те же данные снова sql-postgres и снова (запуск дополнительных sql-postgres операций ввода-вывода при sql-syntax отсутствии стабильного кеша), например:

              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]

Обновление от 29 января 2013 г.

Наконец, начиная sql-select с версии 8.4, Postgres поддерживает query-performance Window Function, что означает, что вы можете pgsql написать что-то настолько sql-query простое и эффективное, как:

Запрос №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
 );