Início / dba / Perguntas / 334300
Accepted
tuskiomi
Asked: 2023-12-23 11:05:33 +0800 CST

otimizar SELECT MAX() com GROUP BY no Postgres?

  • 772

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

1 respostas

  1. Best Answer

    Infelizmente, o postgres não implementa (ainda) o tipo de verificação de índice necessário para otimizar esta consulta automaticamente, portanto, ele verificará todo o índice.

    É capaz de usar um índice em (a,b) para otimizar "max(b) WHERE a=..." e também "WHERE a=... ORDER BY b DESC LIMIT 1" que retorna a linha inteira com maior valor de b (isso pode ser mais útil do que apenas max() se você realmente quiser outras colunas). Mas isso é apenas para um valor de a, ou vários em um loop aninhado, e não para toda a tabela, como você está fazendo.

    Supondo que você tenha uma tabela separada "canais" com a chave primária "channel_id" que é referenciada pelas mensagens da sua tabela, é fácil emular isso manualmente.

    O Postgres sabe como encontrar a linha desejada usando um índice se você solicitar apenas um valor de channel_id. Portanto, o truque é fazer isso para cada valor de channel_id, usando uma subconsulta dependente (se você quiser apenas a coluna max()) ou uma junção LATERAL (se você também quiser outras colunas, como o conteúdo da última mensagem).

    Isso resulta em uma varredura de índice nas mensagens por valor de channel_id. Portanto, o tempo está em O(número de canais * log(número de mensagens)) que deve ser muito mais rápido do que verificar toda a tabela de mensagens. Além disso, ele acessa apenas as páginas com as mensagens mais recentes, portanto não destrói seu cache.

    Crie dados de teste:

    CREATE UNLOGGED TABLE messages( ts INT NOT NULL, channel_id INT NOT NULL );
INSERT INTO messages SELECT n,n%1000 FROM generate_series(1,1000000) n;
CREATE INDEX ON messages( channel_id, ts );

CREATE UNLOGGED TABLE channels ( channel_id INT PRIMARY KEY );
INSERT INTO channels SELECT DISTINCT channel_id FROM messages;
VACUUM ANALYZE;

    Consultas lentas que leem a tabela inteira (ou índice):

    -- SLOW
EXPLAIN ANALYZE SELECT channel_id, max(ts) FROM messages GROUP BY channel_id;

 Finalize GroupAggregate  (cost=11734.85..11988.20 rows=1000 width=8) (actual time=58.321..60.128 rows=1000 loops=1)
   Group Key: channel_id
   ->  Gather Merge  (cost=11734.85..11968.20 rows=2000 width=8) (actual time=58.315..59.871 rows=3000 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=10734.83..10737.33 rows=1000 width=8) (actual time=54.080..54.119 rows=1000 loops=3)
               Sort Key: channel_id
               Sort Method: quicksort  Memory: 56kB
               Worker 0:  Sort Method: quicksort  Memory: 56kB
               Worker 1:  Sort Method: quicksort  Memory: 56kB
               ->  Partial HashAggregate  (cost=10675.00..10685.00 rows=1000 width=8) (actual time=53.872..53.958 rows=1000 loops=3)
                     Group Key: channel_id
                     Batches: 1  Memory Usage: 129kB
                     Worker 0:  Batches: 1  Memory Usage: 129kB
                     Worker 1:  Batches: 1  Memory Usage: 129kB
                     ->  Parallel Seq Scan on messages  (cost=0.00..8591.67 rows=416667 width=8) (actual time=0.009..16.164 rows=333333 loops=3)
 Planning Time: 0.167 ms
 Execution Time: 60.236 ms

-- SLOW
EXPLAIN ANALYZE SELECT channel_id, max(ts) 
FROM channels JOIN messages USING (channel_id)
GROUP BY channel_id;

 Finalize GroupAggregate  (cost=12860.74..13114.09 rows=1000 width=8) (actual time=94.136..96.019 rows=1000 loops=1)
   Group Key: channels.channel_id
   ->  Gather Merge  (cost=12860.74..13094.09 rows=2000 width=8) (actual time=94.131..95.758 rows=3000 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort  (cost=11860.72..11863.22 rows=1000 width=8) (actual time=92.507..92.542 rows=1000 loops=3)
               Sort Key: channels.channel_id
               Sort Method: quicksort  Memory: 56kB
               Worker 0:  Sort Method: quicksort  Memory: 56kB
               Worker 1:  Sort Method: quicksort  Memory: 56kB
               ->  Partial HashAggregate  (cost=11800.89..11810.89 rows=1000 width=8) (actual time=92.299..92.386 rows=1000 loops=3)
                     Group Key: channels.channel_id
                     Batches: 1  Memory Usage: 129kB
                     Worker 0:  Batches: 1  Memory Usage: 129kB
                     Worker 1:  Batches: 1  Memory Usage: 129kB
                     ->  Hash Join  (cost=27.50..9717.56 rows=416667 width=8) (actual time=0.154..58.426 rows=333333 loops=3)
                           Hash Cond: (messages.channel_id = channels.channel_id)
                           ->  Parallel Seq Scan on messages  (cost=0.00..8591.67 rows=416667 width=8) (actual time=0.004..16.329 rows=333333 loops=3)
                           ->  Hash  (cost=15.00..15.00 rows=1000 width=4) (actual time=0.143..0.143 rows=1000 loops=3)
                                 Buckets: 1024  Batches: 1  Memory Usage: 44kB
                                 ->  Seq Scan on channels  (cost=0.00..15.00 rows=1000 width=4) (actual time=0.006..0.060 rows=1000 loops=3)
 Planning Time: 0.127 ms
 Execution Time: 96.066 ms

    Consulta muito mais rápida que encontra a linha mais recente para cada channel_id imediatamente usando o índice, usando uma subconsulta dependente que seleciona max(ts):

    -- FAST
EXPLAIN ANALYZE SELECT channel_id, 
(SELECT max(ts) FROM messages m WHERE m.channel_id=c.channel_id) 
FROM channels c;

 Seq Scan on channels c  (cost=0.00..482.00 rows=1000 width=8) (actual time=0.023..7.308 rows=1000 loops=1)
   SubPlan 2
     ->  Result  (cost=0.46..0.47 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1000)
           InitPlan 1 (returns $1)
             ->  Limit  (cost=0.42..0.46 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1000)
                   ->  Index Only Scan using messages_channel_id_ts_idx on messages m  (cost=0.42..32.42 rows=1000 width=4) (actual time=0.007..0.007 rows=1 loops=1000)
                         Index Cond: ((channel_id = c.channel_id) AND (ts IS NOT NULL))
                         Heap Fetches: 0
 Planning Time: 0.072 ms
 Execution Time: 7.349 ms

    Variante usando LATERAL que tem as seguintes vantagens: pode retornar mais colunas da mensagem caso você precise delas, e pode retornar as últimas ou N últimas mensagens (basta alterar o LIMITE) por canal.

    EXPLAIN ANALYZE SELECT * FROM channels c
LEFT JOIN LATERAL (
 SELECT * FROM messages m WHERE m.channel_id=c.channel_id
 ORDER BY ts DESC LIMIT 1) USING(channel_id);


 Nested Loop Left Join  (cost=0.42..492.00 rows=1000 width=8) (actual time=0.085..10.320 rows=1000 loops=1)
   ->  Seq Scan on channels c  (cost=0.00..15.00 rows=1000 width=4) (actual time=0.019..0.109 rows=1000 loops=1)
   ->  Subquery Scan on unnamed_subquery  (cost=0.42..0.47 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1000)
         Filter: (c.channel_id = unnamed_subquery.channel_id)
         ->  Limit  (cost=0.42..0.45 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1000)
               ->  Index Only Scan using messages_channel_id_ts_idx on messages m  (cost=0.42..29.93 rows=1000 width=8) (actual time=0.009..0.009 rows=1 loops=1000)
                     Index Cond: (channel_id = c.channel_id)
                     Heap Fetches: 0
 Planning Time: 0.313 ms
 Execution Time: 10.411 ms

    A sintaxe LATERAL JOIN é um pouco estranha. Se você deseja uma linha para um channel_id que não possui mensagens, você precisa usar LEFT JOIN, e isso requer uma condição de junção (USING(channel_id)). Mas por ser um LATERAL JOIN, a tabela direita na junção depende da linha esquerda, portanto esta condição já está especificada nela. Portanto, há um pouco de duplicação.

    • 1

relate perguntas