mysql相关子查询优化_sql-优化GROUP BY查询以检索每个我们的最新行

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

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