概括
我有一个包含事件源的表和一个包含事件的表。我需要一个查询,它可以为我提供N
每个来源的最新事件(N
从 1 到 100)。目前,我正在使用执行 a 的子查询ROW_NUMBER() OVER (PARTITION BY "EventSourceId" ORDER BY ...) as rankRecent
和过滤 的外部查询来执行此操作WHERE rankRecent <= @N
。
结果EXPLAIN ANALYZE
显示它正在使用我的索引作为partition
andorder by
子句,但它仍在对整个表进行排名,并且显然期望找到 600 万个结果,但实际上只有 22000 个。我试图找出是否有:(1)一种更好的方法来获取N
每个事件源的最新事件,或者(2)一种向查询规划器暗示它不需要严格对大多数事件进行排名的方法该表,因为仅使用前几个条目。
另外,该查询还有第二个用例,我什至不知道如何开始为其建立索引。这不是这个问题的主旨;我提及它只是为了包含所有可能相关的内容。
细节
数据设置
CREATE TABLE "EventSources"
(
"Id" uuid NOT NULL,
"Name" character varying(100),
CONSTRAINT "PK_EventSources" PRIMARY KEY ("Id")
);
CREATE TABLE "Events"
(
"Id" uuid NOT NULL,
"EventSourceId" uuid NOT NULL,
"Time" timestamp with time zone,
"AltKey" character varying(100),
CONSTRAINT "PK_Events" PRIMARY KEY ("Id"),
CONSTRAINT "FK_Events_EventSources_EventSourceId" FOREIGN KEY ("EventSourceId") REFERENCES "EventSources" ("Id") MATCH SIMPLE ON UPDATE NO ACTION ON DELETE RESTRICT
);
CREATE INDEX "IX_Events_EventSourceId_Time_Desc_AltKey_Desc" ON "Events" USING btree
(
"EventSourceId" ASC,
"Time" DESC NULLS LAST,
"AltKey" DESC NULLS LAST
);
一些其他可能相关的信息:
SELECT version(); --PostgreSQL 13.12, compiled by Visual C++ build 1914, 64-bit
SELECT COUNT(*) FROM "EventSources"; --29,000ish
SELECT COUNT(*) FROM "Events"; --20,000,000ish
SELECT COUNT(*) FROM (SELECT DISTINCT "EventSourceId" FROM "Events") sub; --5,000ish. Most of the "EventSources" don't have "Events" but are used for other things in the db
查询
这是我试图优化的查询:
SELECT
*
FROM
(
SELECT
"Events".*,
ROW_NUMBER() OVER (
PARTITION BY "Events"."EventSourceId"
ORDER BY
"Events"."Time" DESC NULLS LAST,
"Events"."AltKey" DESC NULLS LAST
) as rankRecent
FROM
"Events"
--WHERE "Events"."Time" < @LimitTime
) sub
WHERE
rankRecent <= @N; -- @N is in the range 1 to 100.
用例
以下是查询的用例:
- 我正在加载一个仪表板,该仪表板显示每个事件源的最新数据的聚合计算,并根据这些计算选择要显示的事件源以及显示顺序。
- 我正在调查昨天凌晨 3:14:15 发生的一个问题,并且对于给定的相关事件源集合,我需要查看截至该时间的 100 个事件,以便我可以了解可能发生的情况一直出错。在这种情况下,
WHERE
查询中的注释子句将被取消注释,并且它还可能连接到另一个表以过滤与特定上下文相关的事件源。我不知道如何为此建立索引,但这不是问题的重点。
解释
这是结果EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
(在本例中@N
设置为 5):
- 子查询扫描子(成本= 0.56..2637123.43行= 6664000宽度= 66)(实际时间= 0.156..90245.642行= 22613循环= 1)
- 过滤器:(sub.rankrecent <= 5)
- 筛选器删除的行:19963368
- 缓冲区:共享命中=6738934 读取=13332278
- -> WindowAgg(成本=0.56..2387223.43行=19992000宽度=66)(实际时间=0.155..89355.268行=19985981循环=1)
- 缓冲区:共享命中=6738934 读取=13332278
- -> 在“事件”上使用“IX_Events_EventSourceId_Time_Desc_AltKey_Desc”进行索引扫描(成本=0.56..1987383.43行=19992000宽度=58)(实际时间=0.100..82274.745行=19985981循环=1)
- 缓冲区:共享命中=6738934 读取=13332278
- -> 在“事件”上使用“IX_Events_EventSourceId_Time_Desc_AltKey_Desc”进行索引扫描(成本=0.56..1987383.43行=19992000宽度=58)(实际时间=0.100..82274.745行=19985981循环=1)
- 缓冲区:共享命中=6738934 读取=13332278
- 规划时间:0.111 ms
- 执行时间:90247.357 毫秒
我考虑过的替代方法
- 我考虑过使用物化视图来预先完成繁重的工作。但是,该
"Events"
表每秒大约会插入十几次数据,并且每个此类事务可以包含从零到几十行的任意位置。因此,如果视图使用与现有查询相同的查询计划,则在刷新视图的命令完成之前,视图中的数据将完全过时。所以我得出的结论是这个策略没有意义。 - 我还考虑尝试
sequence
在表中添加一个新的整数列"Events"
,然后对该序列进行过滤。但这并不能真正解决问题。每个插入事件的事务可能有也可能没有给定事件源的事件,因此给定事件源的 5 个最近事件可能包括来自例如 5 秒前的事务和 1 小时前的事务的数据,一张是昨天的,一张是上周的,一张是去年的。因此,没有简单的方法可以sequence
在所有数千个不同的事件源之间同步此类事件。此外,事件源可以手动提交过去任意时间丢失的数据,因此排序"Time"
似乎是正确的。 - 我考虑过建立一个表,仅跟踪每个事件源的 50 个最近事件,并删除任何较旧的事件。这样,查询就可以继续对整个表进行排名,因为不会有那么多数据需要排名。然而,该查询将不再满足用例#2;而且,虽然用例 #2 不是这个问题的主旨,但我不能只是将其从查询中删除而不提供替换。
澄清
这个row_number over partition
问题是这里的主要问题。关于用例 #2 的内容以及不知道如何为其建立索引的内容包含在此处,以防它激发某人实现更好的计划、查询或模式;我并不期待解决方案,只是想包含任何可能相关的信息。
v15 中引入了一项新的优化,允许在找到每个分区所需的行数后停止计算排名。这并不神奇,它仍然需要读取所有行,因为它不知道下一个分区从哪里开始,除非读取行直到分区键发生变化。但至少应该节省一些时间。当此优化生效时,它会在计划中显示如下行:
您可能可以通过实施索引跳过扫描来改进这一点。PostgreSQL 不会自动实现这些,但您可以使用递归 CTE 来实现,如项目wiki上所示(尽管自从我上次访问它以来,看起来可能有人对它进行了一些修改)。
由于您已经有一个包含不同事件源的表,因此您可以通过横向连接来使用它,而不是使用 CTE,如下所示:(我不会在键盘上用大写字母和引号跳来跳去,我留给你吧)
EventSources 中未使用的行会稍微减慢速度,但它仍然可能比您当前的速度快得多。
对于你提到的相关问题,我不知道我没有仔细阅读过。一旦您确认此方法对您有效,如果您还有其他问题,请提出另一个问题。