PostgreSQL – 获取具有列的Max值的行

按照查询成本，我指的是Postgres的基于成本的优化器的成本估计(Postgres的默认xxx_cost值)，这是一个需要的I / O和CPU资源的权重函数估计;您可以通过启动PgAdminIII并对“查询/解释选项”设置为“分析”的查询运行“查询/说明(F7)”，

> Quassnoy的查询具有745k(！)的成本估计，并在1.3秒内完成(给定复合索引(usr_id，trans_id，time_stamp))
> Bill的查询的成本估计为93k，并在2.9秒内完成(假设复合索引为(usr_id，trans_id))
>下面的查询#1具有16k的成本估计，并且在800ms内完成(给定复合索引(usr_id，trans_id，time_stamp))
>下面的查询#2具有14k的成本估计，并且在800ms内完成(给定复合函数索引(usr_id，EXTRACT(EPOCH FROM time_stamp)，trans_id))

>这是Postgres特定的

>下面的查询#3(Postgres 8.4)具有与(或好于)查询#2(给定复合索引(usr_id，time_stamp，trans_id))相比的成本估计和完成时间;它有扫描生命表只有一次的优点，如果你暂时增加(如果需要)work_mem以适应内存中的排序，它将是迄今为止所有查询中最快的。

上面的所有时间包括检索完整的10k行结果集。

您的目标是最小的成本估算和最少的查询执行时间，并强调估计成本。查询执行可以显着依赖于运行时条件(例如，相关行是否已经完全缓存在存储器中)，而成本估计不是。另一方面，请记住，成本估计正是这个，估计。

当在无负载的专用数据库上运行时(例如，在开发PC上使用pgAdminIII)，获得最佳查询执行时间。查询时间将根据实际机器负载/数据访问扩展而变化。当一个查询出现比另一个查询稍快(<20％)但具有高得多的成本时，通常选择具有较高执行时间但较低成本的查询通常是更明智的。 当您期望在运行查询时，您的生产机器上的内存没有竞争(例如，RDBMS缓存和文件系统缓存不会被并发查询和/或文件系统活动损坏)，那么您获得的查询时间在独立(例如pgAdminIII在开发PC上)模式将是代表性的。如果在生产系统上存在争用，查询时间将与估计的成本比例成比例地降低，因为具有较低成本的查询不依赖于高速缓存，而具有较高成本的查询将重复访问相同的数据(触发在没有稳定高速缓存的情况下的附加I / O)，例如：

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生活一次。

查询#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支持Window Function意味着你可以写一些简单有效的：

查询#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
 );