为了获得最佳读取性能,您需要一个多列索引:
CREATE INDEX log_combo_idx
ON log (user_id, log_date DESC NULLS LAST);
要使仅索引扫描成为可能,请使用2987661237728838838657子句(Postgres 11或更高版本)在覆盖索引中添加原本不需要的列2987661237728838838656:
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);
为什么是user_id?
日期范围中未使用的索引查询
对于每个user_id或小表中的几行,通常最快,最简单的是combo1:
在每个GROUP BY组中选择第一行?
对于每个combo1中的许多行,索引跳过扫描(或松散索引扫描)的效率要高得多。 直到Postgres 12才实现该功能-Postgres 13正在进行工作,但是有许多方法可以有效地对其进行仿真。
通用表表达式需要Postgres 8.4+。
combo1需要Postgres 9.3+。
以下解决方案超出了Postgres Wiki所涵盖的范围。
1.没有具有唯一用户的单独表
使用单独的combo1表,下面2中的解决方案通常更简单,更快速。 向前跳。
1a。 具有combo1的递归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;
方便地检索单列或整行。 该示例使用表的整个行类型。 其他变体是可能的。
要断言在先前的迭代中找到一行,请测试单个NOT NULL列(如主键)。
有关此查询的更多说明,请参见第2b章。 下面。
有关:
查询每行最后N个相关行
GROUP BY一列,而在PostgreSQL中按另一列排序
2.具有单独的combo1表
只要保证每个相关combo1的一行正确,表格布局就无关紧要。 例:
CREATE TABLE users (
user_id serial PRIMARY KEY
, username text NOT NULL
);
理想情况下,该表在物理上与combo1表同步排序。 看到:
优化Postgres时间戳查询范围
或它足够小(低基数)几乎没有关系。 否则,对查询中的行进行排序可以帮助进一步优化性能。 参见刚亮的加成。 如果combo1表的物理排序顺序恰巧与(log_date, payload)::combo上的索引匹配,则这可能无关紧要。
2a。 combo1加入
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;
combo1允许在同一查询级别上引用前面的combo1项目。 看到:
LATERAL和PostgreSQL中的子查询有什么区别?
导致每个用户一次索引(仅)查询。
没有为combo1表中缺少的用户返回任何行。 通常,执行参考完整性的外键约束将排除这种情况。
另外,在combo1中没有匹配条目的用户将没有行-符合原始问题。 为了使这些用户留在结果中,请使用combo1而不是(log_date, payload)::combo:
多次调用带有数组参数的set-returning函数
使用combo1而不是combo1来为每个用户检索多于一行(但不是全部)。
有效地,所有这些都做相同的事情:
JOIN LATERAL ... ON true
CROSS JOIN LATERAL ...
, LATERAL ...
不过,最后一个优先级较低。 显式combo1在逗号前绑定。 这种细微的差别可能与更多的联接表有关。 看到:
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;
像上面的combo1一样,此变体包括所有用户,即使在(log_date, payload)::combo中没有条目。您也会获得NULL的combo1,如果需要,可以在外部查询中使用WHERE子句轻松进行过滤。
Nitpick:在外部查询中,您无法区分子查询未找到行还是所有列值都碰巧为NULL-结果相同。 您需要在子查询中有combo1列,以避免这种歧义。
相关子查询只能返回单个值。 您可以将多个列包装为复合类型。 但是为了以后进行分解,Postgres需要一种众所周知的复合类型。 只有提供列定义列表,才能分解匿名记录。
使用注册类型,例如现有表的行类型。 或使用298766123774561515872显式(永久)注册复合类型。或创建临时表(在会话结束时自动删除)以临时注册其行类型。 转换语法:(log_date, payload)::combo
最后,我们不想在同一查询级别上分解combo1。 由于查询计划器的弱点,这将为每个列评估一次子查询(在Postgres 12中仍然适用)。 而是,使其成为子查询并在外部查询中分解。
有关:
从每组的第一行和最后一行获取值
用100k日志条目和1k用户演示所有4个查询:
db <>在这里拨弄-第11页
旧的sqlfiddle-pg 9.6