避免在使用联接条件运行的窗口函数下进行全表扫描

Question

给定一个数据库表events，其中包含列：event_id、correlation_id、username、create_timestamp。它包含超过100万条记录。

我正在努力解决一个问题：对于特定用户的每个事件，都显示其最新的同级事件。同胞事件是指具有相同correlation_id的事件。我使用的查询如下：

SELECT 
"events"."event_id" AS "event_id", 
"latest"."event_id" AS "latest_event_id" 
FROM 
events "events" 
JOIN (
SELECT 
"latest"."correlation_id" AS "correlation_id", 
"latest"."event_id" AS "event_id", 
ROW_NUMBER () OVER (
PARTITION BY "latest"."correlation_id" 
ORDER BY 
"latest"."create_timestamp" ASC
) AS "rn" 
FROM 
events "latest"
) "latest" ON (
"latest"."correlation_id" = "events"."correlation_id" 
AND "latest"."rn" = 1
) 
WHERE 
"events"."username" = 'user1'

它得到了正确的结果列表，但会导致必须修复的性能问题。以下是查询的执行计划：

Hash Right Join  (cost=13538.03..15522.72 rows=1612 width=64)
Hash Cond: (("latest".correlation_id)::text = ("events".correlation_id)::text)
->  Subquery Scan on "latest"  (cost=12031.35..13981.87 rows=300 width=70)
Filter: ("latest".rn = 1)
->  WindowAgg  (cost=12031.35..13231.67 rows=60016 width=86)
->  Sort  (cost=12031.35..12181.39 rows=60016 width=78)
Sort Key: "latest_1".correlation_id, "latest_1".create_timestamp
->  Seq Scan on events "latest_1"  (cost=0.00..7268.16 rows=60016 width=78)
->  Hash  (cost=1486.53..1486.53 rows=1612 width=70)
->  Index Scan using events_username on events "events" (cost=0.41..1486.53 rows=1612 width=70)
Index Cond: ((username)::text = 'user1'::text)

从该计划中，我可以得出结论，性能问题主要是由表中所有事件的最新事件计算引起的，这占用了成本的约80%。此外，即使用户根本没有事件，它也会执行计算。理想情况下，我希望查询执行以下对我来说更有效的步骤：

1. 按用户查找所有事件
2. 对于步骤1中的每个事件，找到所有兄弟，对它们进行排序，得到第一个

为了简化讨论，让我们将所有必需的索引视为已为所需列创建。在我看来，这个问题并不能完全通过创建索引来解决。

有什么可以改进性能的想法吗可能有重写查询或调整表配置的选项。

请注意，这个问题在商业意义上被明显混淆了，以清楚地展示我面临的技术问题。

Answer 1

窗口函数必须扫描整个表。它不知道你真的只对第一个值感兴趣。横向连接可以表现得更好，而且可读性更强：

SELECT 
e.event_id, 
latest.latest_event_id
FROM 
events AS e
CROSS JOIN LATERAL
(SELECT
l.event_id AS latest_event_id
FROM
events AS l
WHERE
l.correlation_id = e.correlation_id 
ORDER BY l.create_timestamp
FETCH FIRST 1 ROWS ONLY
) AS latest
WHERE e.username = 'user1';

支持的完美指数

CREATE INDEX ON event (correlation_id, create_timestamp);

Answer 2

所有那些不必要的双引号都让我眼睛流血。

如果返回的行数相当低，即"user1"相当特定，那么使用横向连接应该非常快。

explain analyze SELECT 
events.event_id AS event_id, 
latest.event_id AS latest_event_id 
FROM 
events "events" 
cross JOIN lateral (
SELECT 
latest.event_id AS event_id 
FROM events latest
WHERE latest.correlation_id=events.correlation_id 
ORDER by create_timestamp ASC limit 1
) latest 
WHERE 
events.username = 'user1';

您需要username上的索引和(correlation_id, create_timestamp)上的索引

如果返回的行数很大，那么当前的查询(批量预计算)可能会更好。但是，如果您使用DISTINCT ON而不是窗口函数来提取每个correlation_id的最新值，则会更快。不幸的是，规划者不理解这些查询是等效的，因此不会根据其认为更快的内容在它们之间进行相互转换。

Answer 3

一个可以提高效率的选项是重写查询过滤"rn〃=1，以在连接表时减少结果行：

WITH "latestCte"("correlation_id", "event_id") as (SELECT 
"correlation_id", 
"event_id", 
ROW_NUMBER () OVER (
PARTITION BY "correlation_id" 
ORDER BY 
"create_timestamp" ASC
) AS "rn" 
FROM 
events)
SELECT 
"events"."event_id" AS "event_id", 
"latest"."event_id" AS "latest_event_id" 
FROM 
events "events" 
JOIN (
SELECT "correlation_id", "event_id" FROM "latestCte" WHERE "rn" = 1
) "latest" ON (
"latest"."correlation_id" = "events"."correlation_id" 
) 
WHERE 
"events"."username" = 'user1'

希望它能有所帮助，我也很想看看这个查询的执行计划。顺致敬意，

Answer 4

如果不能访问数据，我真的只是抛出一些想法。。。

1. 与其使用子查询，不如尝试物化CTE

2. 您可以尝试distinct on，而不是row_number分析。老实说，我预计不会有任何收获。在数据库级别上基本上是一样的

两者的样品：

with latest as materialized (
SELECT distinct on ("correlation_id")
"correlation_id", "event_id" 
FROM events
order by
"correlation_id", "create_timestamp" desc
)
SELECT 
e."event_id", 
l."event_id" AS "latest_event_id" 
FROM 
events "events" e
join latest l ON
l."correlation_id" = e."correlation_id" 
WHERE 
e."username" = 'user1'

额外的建议——如果你一遍又一遍地这样做，我会考虑为"创建一个临时表或物化视图；最新，"；通过coorelation_id而不是每次重新运行子查询(或CTE)来包括索引。这将是我一次又一次的痛苦。

还有一个建议——如果可能的话，去掉对象名称中的双引号。也许只有我一个人，但我觉得他们很残忍。除非你的域名中有空格、保留字或强制性大写字母(请不要这样做)，否则这些问题会产生比解决问题更多的问题。我把它们保存在上面列出的查询中，但这让我很痛苦

最后一条评论要追溯到了解您的数据。。。由于row_number和distinct-on是相对昂贵的操作，因此通过引入"；user1"；限制这是完全未经测试的，但像这样：

SELECT distinct on (e1.correlation_id)
e1.correlation_id, e1.event_id
FROM events e1
join events e2 on
e1.correlation_id = e2.correlation_id and
e2.username = 'user1'
order by
e1.correlation_id, e1.create_timestamp desc

Answer 5

尽管我喜欢其他人建议的LATERAL JOIN方法，但当涉及到仅获取1个字段时，我对使用该方法和使用如下子查询持50%的看法。(如果你需要使用相同的逻辑来获取多个字段，那么横向是最好的方法！)

我想知道它们中的任何一个是否会表现得更好，大概它们是由SQL引擎以非常相似的方式执行的。

SELECT e.event_id, 
(SELECT l.event_id
FROM events AS l
WHERE l.correlation_id = e.correlation_id 
ORDER BY l.create_timestamp ASC -- shouldn't this be DESC?
FETCH FIRST 1 ROWS ONLY) as latest_event_id
FROM events AS e
WHERE e.username = 'user1';

注意：您当前正在请求OLDEST相关记录。在你的帖子中，你说你正在寻找"；最近的兄弟事件"最新的"；IMHO表示最近的一个，因此它将具有最大的create_timestamp，这意味着您需要按从高到低的字段排序，然后取第一个。

编辑：与上面建议的相同，对于这种方法，您还需要correlation_id和create_timestamp上的索引

CREATE INDEX ON event (correlation_id, create_timestamp);

您甚至可能希望包含event_id以避免书签查找，尽管这些页面可能无论如何都在缓存中，所以不确定它是否真的有那么大帮助。

CREATE INDEX ON event (correlation_id, create_timestamp, event_id);

附言：将correlation_id添加到events_username索引也是如此。。。但这一切都是针对这个(可能是简化的)查询的，请记住，更多(更大)的索引将在其他地方带来一些开销，即使它们可能在其他地方产生巨大的好处。。。这总是一种妥协。