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

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

стоимостью запроса, ниже, я обращаюсь к оценке затрат основанного на стоимости оптимизатора Пост-ГРЭС (со значением по умолчанию Пост-ГРЭС xxx_cost значения), который является взвешенной функциональной оценкой необходимого ввода-вывода и ресурсов ЦП; можно получить, это путем разжигания PgAdminIII и выполнения "Запрашивает/Объясняет (F7)" на запросе с, "Запрашивают/Объясняют опции" набор, чтобы "Проанализировать"

• , запрос Quassnoy имеет оценку затрат 745k (!) и завершается за 1,3 секунды (учитывая составной индекс на (usr_id, trans_id, time_stamp))
• , запрос счета имеет оценку затрат 93k и завершается за 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))
  • это Определенное для пост-ГРЭС
• , Запрос № 3 ниже (Пост-ГРЭС 8.4 +) имеет время оценки затрат и завершения, сопоставимое с (или лучше, чем) запрос № 2 (учитывая составной индекс на (usr_id, time_stamp, trans_id)); это имеет преимущество сканирования lives таблица только однажды и, должны Вы временно увеличиваться (в случае необходимости) work_mem для размещения вида в памяти, это будет безусловно самым быстрым из всех запросов.

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

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

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

обновление Запроса Запроса № 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]

-- 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

Наконец, с версии 8.4, поддержки Пост-ГРЭС Функция Окна значение можно записать что-то столь же простое и эффективный как:

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