Início / user-280857

tuskiomi's questions

Martin Hope
tuskiomi
Asked: 2023-12-23 11:05:33 +0800 CST
  • 5

Estou tentando acelerar a seguinte consulta no postgres:

select MAX(msg."timestamp") AS latestDate, msg.channel_id from message msg group by msg.channel_id

o explainé como tal:

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

O DDL da tabela é assim:

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);

e por fim, o 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

Como você pode ver, mesmo com um índice btree, a operação ainda leva 7,6 segundos, a maior parte dos quais é gasta apenas na varredura paralela do índice. Não sei como acelerar isso ainda mais. o índice tem um tamanho relativo de 5,7 G e dou à minha instância 6 GB de RAM, o que deve ser mais que suficiente para uma pesquisa btree max. Eu defini minhas configurações de acordo com pgtune ( https://pgtune.leopard.in.ua/ ).

Há alguma coisa que esteja faltando em face das coisas?

postgresql