优化GROUP BY查询以检索每个用户的最新行

Question

我在Postgres 9.2中有以下用于用户消息（简化形式）的日志表：

CREATE TABLE log (
    log_date DATE,
    user_id  INTEGER,
    payload  INTEGER
);

每个用户每天最多包含一条记录。在300天之内，每天大约有50万条记录。每个用户的有效负载都在增加（如果很重要）。

我想有效地检索每个用户在特定日期之前的最新记录。我的查询是：

SELECT user_id, max(log_date), max(payload) 
FROM log 
WHERE log_date <= :mydate 
GROUP BY user_id

这非常慢。我也尝试过：

SELECT DISTINCT ON(user_id), log_date, payload
FROM log
WHERE log_date <= :mydate
ORDER BY user_id, log_date DESC;

具有相同的计划，并且速度同样慢。

到目前为止，我在上只有一个索引log(log_date)，但并没有太大帮助。

我有一个users包含所有用户的表。我还想为某些用户（具有的用户payload > :value）检索结果。

我是否应该使用其他任何索引来加快速度，或者通过其他任何方式来实现我想要的目标？

Answer 1

为了获得最佳读取性能，您需要一个多列索引：

CREATE INDEX log_combo_idx
ON log (user_id, log_date DESC NULLS LAST);

为了使 仅索引扫描成为
可能，请在子句payload的覆盖索引中添加本来不需要的列INCLUDE（Postgres 11或更高版本）：

CREATE INDEX log_combo_covering_idx
ON log (user_id, log_date DESC NULLS LAST) INCLUDE (payload);

看：

• PostgreSQL中的覆盖索引对JOIN列有帮助吗？

较旧版本的备用广告：

CREATE INDEX log_combo_covering_idx
ON log (user_id, log_date DESC NULLS LAST, payload);

为什么DESC NULLS LAST呢？

• 日期范围中未使用的索引查询

对于每个或小表 _ 几_ 行，通常最快，最简单：user_id``DISTINCT ON

• 在每个GROUP BY组中选择第一行？

对于 _ 许多_ 每行user_id的 索引跳跃扫描 （或 松散索引扫描
）
是（多）更有效。在Postgres
12之前尚未实现该功能-Postgres
14正在进行中。但是，有一些方法可以有效地对其进行仿真。

常用表表达式需要Postgres
8.4+ 。
LATERAL需要Postgres 9.3+ 。
以下解决方案超出了 Postgres
Wiki 所涵盖的范围。

1.没有具有唯一用户的单独表

使用单独的users表格，下面 2. 中的解决方案通常更简单，更快捷。向前跳。

1a。递归CTE与LATERAL加盟

WITH RECURSIVE cte AS (
   (                                -- parentheses required
   SELECT user_id, log_date, payload
   FROM   log
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT l.user_id, l.log_date, l.payload
      FROM   log l
      WHERE  l.user_id > c.user_id  -- lateral reference
      AND    log_date <= :mydate    -- repeat condition
      ORDER  BY l.user_id, l.log_date DESC NULLS LAST
      LIMIT  1
      ) l
   )
TABLE  cte
ORDER  BY user_id;

这很容易检索任意列，并且在当前的Postgres中可能最好。在第 2a 章中有更多解释 。 以下。

1b。具有相关子查询的递归CTE

WITH RECURSIVE cte AS (
   (                                           -- parentheses required
   SELECT l AS my_row                          -- whole row
   FROM   log l
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT l                            -- whole row
           FROM   log l
           WHERE  l.user_id > (c.my_row).user_id
           AND    l.log_date <= :mydate        -- repeat condition
           ORDER  BY l.user_id, l.log_date DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  (c.my_row).user_id IS NOT NULL       -- note parentheses
   )
SELECT (my_row).*                              -- decompose row
FROM   cte
WHERE  (my_row).user_id IS NOT NULL
ORDER  BY (my_row).user_id;

方便地检索 单列 或 整行 。该示例使用表的整个行类型。其他变体也是可能的。

要断言在先前的迭代中找到一行，请测试单个NOT NULL列（如主键）。

有关此查询的更多说明，请参见第2b章。 以下。

有关的：

• 查询每行最后N个相关行
• GROUP BY一列，而在PostgreSQL中按另一列排序

2.附独立users桌子

只要user_id保证每个相关项仅一行，表布局就无关紧要。例子：

CREATE TABLE users (
   user_id  serial PRIMARY KEY
 , username text NOT NULL
);

理想情况下，表在物理上与log表同步排序。看：

• 优化Postgres时间戳查询范围

或它足够小（低基数）几乎没有关系。否则，对查询中的行进行排序可以帮助进一步优化性能。参见刚亮的加成。
如果表的物理排序顺序users恰好与on上的索引匹配log，则可能无关紧要。

2a。LATERAL加入

SELECT u.user_id, l.log_date, l.payload
FROM   users u
CROSS  JOIN LATERAL (
   SELECT l.log_date, l.payload
   FROM   log l
   WHERE  l.user_id = u.user_id         -- lateral reference
   AND    l.log_date <= :mydate
   ORDER  BY l.log_date DESC NULLS LAST
   LIMIT  1
   ) l;

JOIN LATERAL允许FROM在同一查询级别上引用前面的项目。看：

• LATERAL和PostgreSQL中的子查询有什么区别？

导致每个用户一次索引（仅）查询。

对于users表中缺少的用户，不返回任何行。通常，强制引用完整性的 外键 约束将排除这种情况。

同样，没有匹配项的用户也没有行log-符合原始问题。为了使这些用户留在结果中，请使用 LEFT JOIN LATERAL ... ON true 而不是CROSS JOIN LATERAL：

• 多次调用带有数组参数的set-returning函数

使用 LIMIT n 而不是为每个用户LIMIT 1检索 多行 （但不是全部）。

有效地，所有这些都做相同的事情：

JOIN LATERAL ... ON true
CROSS JOIN LATERAL ...
, LATERAL ...

不过，最后一个优先级较低。显式JOIN绑定在逗号之前。这种细微的差别可能与更多的联接表有关。看：

• Postgres查询中的“对表的FROM子句条目的无效引用”

2b。相关子查询

从 单行中* 检索 单列的 好选择。代码示例： *

• 优化分组最大查询

多个列 也可以这样做，但是您需要更多的技巧：

CREATE TEMP TABLE combo (log_date date, payload int);

SELECT user_id, (combo1).*              -- note parentheses
FROM (
   SELECT u.user_id
        , (SELECT (l.log_date, l.payload)::combo
           FROM   log l
           WHERE  l.user_id = u.user_id
           AND    l.log_date <= :mydate
           ORDER  BY l.log_date DESC NULLS LAST
           LIMIT  1) AS combo1
   FROM   users u
   ) sub;

• 与LEFT JOIN LATERAL上述类似，此变体包括 所有 用户，即使没有在中输入log。你得到NULL的combo1，你可以很容易地过滤WHERE外部查询子句如果需要的话。
  Nitpick：在外部查询中，您无法区分子查询未找到行还是所有列值都碰巧为NULL-结果相同。您需要NOT NULL在子查询中创建一列以避免这种歧义。

• 相关的子查询只能返回一个 值 。您可以将多个列包装为复合类型。但是为了稍后进行分解，Postgres需要一种众所周知的复合类型。仅提供列定义列表，才能分解匿名记录。
  使用注册类型，例如现有表的行类型。或使用显式（并永久）注册复合类型CREATE TYPE。或创建一个临时表（在会话结束时自动删除）以临时注册其行类型。强制转换语法：(log_date, payload)::combo

• 最后，我们不想combo1在同一查询级别上进行分解。由于查询计划器的弱点，这将为每个列评估一次子查询（在Postgres 12中仍然适用）。而是，使其成为子查询并在外部查询中分解。

有关的：

• 从每组的第一行和最后一行获取值

演示所有具有100k日志条目和1k用户的4个查询：
db
<>在这里
拨弄 -第11页
旧sqlfiddle-拨第9.6页