要获得最佳读取性能，您需要使用multicolumn index：

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

要使**index only scans**成为可能，请使用INCLUDE子句（Postgres 11或更高版本）在covering index中添加原本不需要的列payload：

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

参见：

• Do covering indexes in PostgreSQL help JOIN columns?

旧版本的回退：

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

为什么是DESC NULLS LAST？

• Unused index in range of dates query

对于每个user_id***很少***行或小表，DISTINCT ON通常是最快和最简单的：

• 是否选择每个GROUP BY组中的第一行？

对于每个user_id有*多*行的情况，index skip scan (or loose index scan)（要）高效得多。这在Postgres 15 (work is ongoing)之前还没有实现。但是有一些方法可以有效地模拟它。
Common Table Expressions需要Postgres8.4 +。
LATERAL需要Postgres9.3 +。
以下解决方案超出了Postgres Wiki的涵盖范围。

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

• 使用单独的users表，下面**2.*中的解决方案通常更简单、更快速。跳过。

1a.使用LATERAL连接的递归CTE

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;

便于检索 * 单列 * 或 * 整行 *。本示例使用表的整行类型。其他变体也是可能的。
要Assert在上一次迭代中找到了一行，请测试单个NOT NULL列（如主键）。

• 有关此查询的详细说明，请参见下面的第2b章。*

相关：

• 每行查询最后N个相关行
• GROUP BY one column, while sorting by another in PostgreSQL

2.使用单独的users表

只要保证每个相关的user_id正好有一行，表格布局就不重要。

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

理想情况下，表的物理排序与log表同步。请参阅：

• 优化时间戳范围上的Postgres查询

或者它足够小（低基数），几乎不起作用。另外，对查询中的行进行排序可以帮助进一步优化性能。See Gang Liang's addition.如果users表的物理排序顺序恰好与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项。请参阅：

• PostgreSQL中的横向连接和子查询有什么区别？

每个用户只进行一次索引（仅-）查找。
对于users表中缺少的用户，不返回任何行。通常，实施参照完整性的外键约束会排除这种情况。
此外，log中没有匹配条目的用户没有行-与原始问题一致。要将这些用户保留在结果中，请使用**LEFT JOIN LATERAL ... ON true**代替CROSS JOIN LATERAL：

• 多次调用带有数组参数的返回集合的函数

使用**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类似，这个变体包括 * all * 用户，即使log中没有条目，也可以得到combo1的NULL，如果需要，可以在外部查询中使用WHERE子句轻松地过滤它。
吹毛求疵：在外部查询中，你无法区分子查询是否没有找到行，或者所有列的值碰巧都是NULL--同样的结果。你需要在子查询中使用NOT NULL列来避免这种歧义。
一个相关子查询只能返回一个单个值。你可以将多个列 Package 成一个复合类型。但是为了以后分解它，Postgres需要一个众所周知的复合类型。匿名记录只能在提供列定义列表的情况下分解。
使用已注册的类型，如现有表的行类型。或者使用CREATE TYPE显式（永久）注册复合类型。或者创建临时表（在会话结束时自动删除）以临时注册其行类型。转换语法：(log_date, payload)::combo
最后，我们不希望在同一查询级别上分解combo1。由于查询计划器中的一个弱点，这将为每列计算一次子查询（在Postgres 12中仍然如此）。相反，将其作为子查询并在外部查询中分解。
相关：

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

使用100k个日志条目和1k个用户演示所有4个查询：

• db〈〉小提琴here *-第11页

老SQLF

这不是一个独立的答案，而是对@Erwin的answer的一个注解。对于2a，横向连接示例，可以通过对users表排序来利用log上索引的局部性来改进查询。

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

其基本原理是，如果user_id值是随机的，则索引查找的开销会很大。通过首先对user_id进行排序，后续的横向联接就像对log的索引进行简单扫描一样。尽管两个查询计划看起来很相似，但运行时间会有很大差异，尤其是对于大型表。
排序的成本是最小的，特别是在user_id字段上有索引的情况下。

也许表上的其他索引会有所帮助。请尝试以下索引：我不确定Postgres是否会最佳地使用distinct on。
所以，我会坚持使用该索引，并尝试这个版本：

select *
from log l
where not exists (select 1
                  from log l2
                  where l2.user_id = l.user_id and
                        l2.log_date <= :mydate and
                        l2.log_date > l.log_date
                 );

这将用索引查找代替排序/分组。这样可能会更快。