我正在尝试加快 postgres 中的以下查询速度:
select MAX(msg."timestamp") AS latestDate, msg.channel_id from message msg group by msg.channel_id
是explain
这样的:
Finalize GroupAggregate (cost=1000.63..2442779.42 rows=305 width=24)
Group Key: channel_id
-> Gather Merge (cost=1000.63..2442770.27 rows=1220 width=24)
Workers Planned: 4
-> Partial GroupAggregate (cost=0.57..2441624.90 rows=305 width=24)
Group Key: channel_id
-> Parallel Index Only Scan using message_channel_id_timestamp on message msg (cost=0.57..2243767.89 rows=39570792 width=24)
JIT:
Functions: 6
Options: Inlining true, Optimization true, Expressions true, Deforming true
该表的 DDL 如下:
CREATE TABLE public.message (
message_pgid bigserial NOT NULL,
id uuid NOT NULL,
"timestamp" timestamptz NOT NULL,
"content" text NOT NULL,
channel_id uuid NOT NULL,
CONSTRAINT message_pk PRIMARY KEY (message_pgid),
CONSTRAINT message_un UNIQUE (channel_id, id)
);
CREATE INDEX message_channel_id_idx ON public.message USING btree (channel_id);
CREATE INDEX message_channel_id_timestamp ON public.message USING btree (channel_id, "timestamp");
CREATE INDEX message_id ON public.message USING btree (id);
CREATE INDEX message_timestamp_idx ON public.message USING btree ("timestamp");
-- public.message foreign keys
ALTER TABLE public.message ADD CONSTRAINT channel_fk FOREIGN KEY (channel_id) REFERENCES public.channel(id) DEFERRABLE;
ALTER TABLE public.message ADD CONSTRAINT message_fk FOREIGN KEY (user_id) REFERENCES public."user"(id);
最后,explain analyze
:
Finalize GroupAggregate (cost=1000.63..2442779.42 rows=305 width=24) (actual time=7631.501..7673.692 rows=597 loops=1)
Group Key: channel_id
-> Gather Merge (cost=1000.63..2442770.27 rows=1220 width=24) (actual time=7631.383..7673.511 rows=1667 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=0.57..2441624.90 rows=305 width=24) (actual time=305.736..6125.479 rows=333 loops=5)
Group Key: channel_id
-> Parallel Index Only Scan using message_channel_id_timestamp on message msg (cost=0.57..2243767.89 rows=39570792 width=24) (actual time=0.557..4938.221 rows=31656633 loops=5)
Heap Fetches: 32082
Planning Time: 4.032 ms
JIT:
Functions: 18
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 12.315 ms, Inlining 193.685 ms, Optimization 122.739 ms, Emission 100.570 ms, Total 429.309 ms
Execution Time: 7684.655 ms
正如你所看到的,即使使用btree索引,操作仍然需要7.6秒,其中大部分花费在并行索引扫描上。我有点不知道如何进一步加快速度。该索引的相对大小为 5.7G,我为我的实例提供了 6GB 的 RAM,这对于 btree 最大搜索来说应该绰绰有余。我已经根据 pgtune ( https://pgtune.leopard.in.ua/ ) 设置了我的设置。
表面上我有什么遗漏的吗?
不幸的是,postgres 还没有实现自动优化此查询所需的索引扫描类型,因此它将扫描整个索引。
它能够使用 (a,b) 上的索引来优化“max(b) WHERE a=...”以及“WHERE a=... ORDER BY b DESC LIMIT 1”,它返回具有最高值的整行b 的值(如果您确实想要其他列,这可能比 max() 更有用)。但这仅适用于 a 的一个值或嵌套循环中的多个值,而不是像您所做的那样适用于整个表。
假设您有一个单独的表“channels”,其主键“channel_id”由表消息引用,则很容易手动模拟它。
如果您只要求一个channel_id 值,Postgres 知道如何使用索引找到您想要的行。因此,技巧是对channel_id 的每个值执行此操作,使用依赖子查询(如果您只需要 max() 列)或 LATERAL 连接(如果您还需要其他列,例如最新消息的内容)。
这会导致对每个channel_id 值的消息进行索引扫描。所以时间是 O(通道数 * log(消息数)),这应该比扫描整个消息表快得多。此外,它只访问包含最新消息的页面,因此不会破坏您的缓存。
创建测试数据:
读取整个表(或索引)的慢查询:
更快的查询,使用索引立即找到每个channel_id的最新行,并使用选择 max(ts) 的依赖子查询:
使用 LATERAL 的变体具有以下优点:可以在需要时从消息中返回更多列,并且可以每个通道返回最新或 N 个最新消息(只需更改 LIMIT)。
LATERAL JOIN 语法有点奇怪。如果您想要一个没有消息的channel_id 行,则需要使用LEFT JOIN,并且这需要连接条件(USING(channel_id))。但因为它是 LATERAL JOIN,所以连接中的右表依赖于左表,所以这个条件已经在其中指定了。所以有一点重复。