我正在尝试加快 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/ ) 设置了我的设置。
表面上我有什么遗漏的吗?