PostgreSQL

PostgreSQL

在具有158k个伪随机行的表上（

usr_id

在0和

10ktrans_id

之间均匀分布，在0和30之间均匀分布），

下面，通过查询成本，我指的是基于Postgres的基于成本的优化器的成本估算（带有Postgres的默认xxx_cost值），它是对所需I / O和CPU资源的加权函数估算；您可以通过启动PgAdminIII并在查询上运行“查询/解释（F7）”并将“查询/解释选项”设置为“分析”来获取此信息。

Quassnoy的查询有745k成本估算（！），并完成了130秒（给出一个复合索引

（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

））; 它具有lives只扫描一次表的优点，并且，如果您临时增加（如果需要）work_mem以容纳内存中的排序，那么它将是所有查询中最快的。
上面所有时间都包括检索全部1万行结果集。

您的目标是最小的成本估算和最短的查询执行时间，重点是估算成本。查询执行可能在很大程度上取决于运行时条件（例如，相关行是否已经完全缓存在内存中），而成本估算却没有。另一方面，请记住，成本估算正是估算值。

当在没有负载的专用数据库上运行时（例如，在开发PC上使用pgAdminIII），可以获得最佳的查询执行时间。查询时间将根据实际的机器负载/数据访问范围而在生产环境中有所不同。当一个查询稍快出现（<20％）比其它但是具有多更高的成本，这将通常是明智的选择具有较高的执行时间，但成本更低。

如果您希望在运行查询时生产机器上的内存没有竞争（例如，并发查询和/或文件系统活动不会破坏RDBMS缓存和文件系统缓存），那么您获得的查询时间在独立模式下（例如，开发PC上的pgAdminIII）将具有代表性。如果生产系统存在争用，查询时间将与估计的成本比率成比例地降低，因为成本较低的查询对缓存的依赖程度不高，而成本较高的查询将反复访问相同的数据（触发在没有稳定缓存的情况下添加其他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 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--  histogramSELECT  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 l2ON  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 tableSELECt 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 );