Melhorar o desempenho da consulta da junção externa esquerda filtrada de tabelas grandes

Este é um plano estranho. Parte da estranheza é que depesz bagunça a apresentação da varredura de heap de bitmap paralela, mostrando os 'Buffers:' para apenas um dos trabalhadores como se fossem os 'Buffers:' de toda a seção da árvore do plano. Acessar a guia "Fonte" fornece informações reais.

O que ainda é muito estranho. Buffers: shared hit=108093sem leituras. Esta seção do plano precisa de quase 850 MB de dados e cada bit deles já está em shared_buffers? Isso ocorre apenas porque você executou essa parametrização de consulta específica repetidamente, mas apenas nos mostrou a execução mais rápida dela? Nesse caso, parece uma maneira bastante irreal de otimizar suas consultas. Como é executá-lo a partir de um cache frio ou executar uma nova parametrização que não foi executada recentemente?

Não há como dizer ao PostgreSQL para esperar encontrar todos os dados necessários para uma consulta específica já no cache. Existe effect_cache_size, mas isso é para quando se espera que uma única execução de consulta atinja os mesmos dados repetidamente, não para quando execuções diferentes atingem todos os mesmos dados. Você poderia mexer nas configurações seq e aleatória de page_cost, mas é improvável que qualquer configuração que você criar seja aplicável globalmente. Se você precisar ajustar as configurações apenas para o escopo desta consulta, seria mais simples ajustar enable_seqscan em vez de outras coisas.

Ou você pode usar uma dica de consulta. Existe uma extensão de terceiros que os implementa, https://github.com/ossc-db/pg_hint_plan . Está até disponível em RDS; Não conheço outros provedores de banco de dados gerenciados.

Alternativamente, um índice em (job_id, file_id,status) deve permitir uma varredura somente de índice nesta tabela, e isso provavelmente pareceria melhor para o planejador do que a varredura seq seria se muitas das páginas estivessem marcadas como totalmente visíveis.

Alguns outros comentários sobre isso: Seu work_mem parece muito baixo se você executa consultas tão grandes rotineiramente; aumentá-lo não resolveria o problema do seqscan, mas poderia tornar o plano rápido ainda mais rápido. Seu IO parece ruim, 40 MB/s parece algo de um laptop de 15 anos, não de um hardware moderno de classe de servidor (embora aparentemente seja para cada trabalhador, na verdade 120 MB/s, o que é melhor, mas ainda não ótimo).

Primeiro, há uma varredura sequencial suspeitamente lenta a 42 MB/s em sua consulta. Como diz jjanes, essa é a velocidade do disco rígido do laptop de 5.400 rpm, a menos que haja muitos outros processos atingindo o disco ao mesmo tempo. São SSDs, então você tem um problema. Se você não estiver usando SSDs em um banco de dados, pense nisso. Recebo cerca de 3 GB/s de leitura de btrfs compactados zstd em meu SSD de desktop barato, de modo que a varredura seq leva cerca de um segundo no exemplo abaixo.

Em ambos os seus planos, ele puxa:

• 302.758 linhas de arquivos com o round_id que você solicitou
• 1.342.121 linhas de processamento_tasks, mas elas são filtradas apenas em job_id. Eles também podem conter muitas linhas para arquivos com round_id incorreto.

Infelizmente, EXPLAIN não informa quais proporções dessas linhas foram buscadas e depois mantidas ou descartadas porque tinham o file_id errado. Você deve investigar com algumas consultas de contagem. Existem dois casos:

• Se a maioria das linhas realmente tivesse bons file_id e fossem mantidas na junção.

Nesse caso você não pode fazer muito, pois é necessário buscar essas linhas. Suas únicas opções são não fazer a consulta ou tornar a busca das linhas mais rápida: SSDs/mais RAM ou tornar o IO menos aleatório e fazer menos agrupando a tabela ou usando um índice de cobertura.

• Se isso jogasse fora uma proporção significativa de linhas.

Neste caso o objetivo deve ser não buscar as linhas que serão descartadas pela junção. Talvez um índice de múltiplas colunas em processing_tasks ajudasse. Você já tem um índice exclusivo em (job_id, file_id, task_id) e ele não é usado, então talvez (file_id,job_id) seja útil.

Criei dados de teste com aproximadamente a mesma distribuição e correlação:

CREATE UNLOGGED TABLE files (
    id         integer                 not null,
    url        character varying(1024) not null,
    bytes      bigint                          ,
    round_id   integer                 not null
);
INSERT INTO files 
    SELECT n, 'https://dba.stackexchange.com/questions/333837/improve-query-performance-of-filtered-left-outer-join-of-big-tables',
    0, 
    random()*50     -- values distributed randomly
--  n/500000        -- values nicely clustered in table
FROM generate_series(1,25000000) n;

ALTER TABLE files ADD PRIMARY KEY(id);
CREATE INDEX files_round_id ON files( round_id );
VACUUM ANALYZE files;

CREATE UNLOGGED TABLE processing_tasks (
    id             bigint                not null,
    task_id        character varying(64) not null,
    status         character varying(1)  not null,
    file_id        integer               not null,
    job_id         bigint                not null
);

TRUNCATE processing_tasks;
INSERT INTO processing_tasks
    SELECT n, 
    'x', 
    'x', 
    n/3, 
    n/18000000-(n%15)
FROM generate_series(1,60000000) n;
VACUUM ANALYZE processing_tasks;
SELECT * FROM pg_stats WHERE tablename='processing_tasks' AND attname='job_id';

ALTER TABLE processing_tasks ADD PRIMARY KEY (id);
CREATE INDEX processing_tasks_file_id ON processing_tasks(file_id);
CREATE INDEX processing_tasks_uniqueness ON processing_tasks(job_id, file_id, task_id);

Para evitar benchmarking array_agg() usei max():

EXPLAIN ANALYZE SELECT files.id, max(b.status)
FROM files
LEFT OUTER JOIN processing_tasks b
    ON (files.id = b.file_id AND b.job_id = 2)
WHERE files.round_id = 10
GROUP BY files.id;

Com work_mem adequado (256 MB), recebo este plano: ( not cached , cached ) que é semelhante ao seu rápido, varredura de índice de bitmap em ambas as tabelas mais hash join. É bastante lento e a varredura do índice de bitmap nos arquivos não é muito útil, pois lê a maior parte da tabela de qualquer maneira, e os SSDs podem ser rápidos em leituras aleatórias, mas isso não é uma solução mágica. Observe que suas tabelas foram armazenadas em cache durante sua execução.

A quantidade de dados lidos dos arquivos é muito grande, devido à coluna de URL falsa que inseri propositalmente. Assim, crio um índice de cobertura:

DROP INDEX files_round_id;
CREATE INDEX files_round_id_cov ON files( round_id ) INCLUDE ( id );

Vamos refazer a mesma consulta: o plano resultante é muito melhor , pois busca round_id no índice e busca file_id diretamente. Isso evita muitos IO em arquivos de tabela, mas funcionará somente se todas as colunas usadas em sua consulta estiverem nesse índice de cobertura. Caso contrário, ainda terá que ser buscado na mesa.

Se sua tabela contiver algumas colunas curtas e colunas grandes como URL, você poderá usar o particionamento vertical e armazenar o URL em uma tabela secundária. Isso é um pouco semelhante ao que o TOAST faz. Isso torna o acesso às colunas da tabela secundária mais lento, mas a tabela principal fica menor. Ou você pode adicionar mais colunas ao índice de cobertura.

Outra solução é CLUSTER os arquivos da tabela, neste caso por (round_id,file_id) mas isso demora um pouco e bloqueia a tabela. A vantagem é que os arquivos com o mesmo round_id são adjacentes na tabela, o que significa que a varredura do índice de bitmap fará principalmente IO sequencial em vez de muitas buscas aleatórias, por isso é mais rápido (observe que este está em cache, por isso é artificialmente rápido).

Nada disso resolve o problema de IO na outra tabela. Então eu tento alguns índices em processamento_tasks...

(job_id,file_id)     no effect, besides you already had it
(file_id,job_id)     no effect
(job_id,file_id) INCLUDE (status)

O último funciona bem, pois permite uma junção de mesclagem que é concluída em menos de 400 ms. No entanto, este índice é redundante com a sua restrição exclusiva, portanto esta combinação deve funcionar melhor:

"processing_tasks_pkey" PRIMARY KEY, btree (id)
"processing_tasks_file_id_job_id" btree (file_id, job_id)
"processing_tasks_uniqueness" btree (job_id, file_id, task_id) INCLUDE (status)

IMO, esta é a melhor opção, a menos que você tenha muitas consultas diferentes do mesmo tipo, todas exigindo seu próprio índice de cobertura especial. Nesse caso, você acabaria com muitos dados duplicados.

Outra opção é apenas desnormalizar e colocar round_id na tabela processing_tasks.

CREATE TABLE processing_tasks_2 AS
SELECT p.*, round_id FROM processing_tasks p
JOIN files f ON (f.id=p.file_id);
CREATE INDEX pt2t ON processing_tasks_2( round_id, job_id, file_id );

Isso funciona bem e é executado em ~ 500 ms sem cache e 255 ms em cache.

CREATE INDEX pt2tc ON processing_tasks_2( round_id, job_id, file_id ) INCLUDE (status);

Isso permite uma junção de mesclagem entre duas varreduras somente de índice, que é a opção mais rápida possível.

Não usar a tabela de arquivos e buscar apenas as linhas de processamento_tasks_2 leva 16 milissegundos, mas dá um resultado diferente, sem a junção à esquerda.

Todas essas otimizações são eficazes, mas são direcionadas: esses índices consomem espaço e recursos para se manterem atualizados. Cabe a você decidir se eles valem esse custo.

NO ENTANTO

Claro que a consulta é lenta, mas se você quiser otimizá-la, isso deve significar que você a executa com frequência. Caso contrário, seria um trabalho em lote às 4h. Se precisar ser executado em menos de 4 segundos, talvez você o execute a cada minuto ou até com mais frequência? Então, por que você precisa desse resultado de 1 milhão de linhas com tanta frequência?

Parece um sistema de gerenciamento de tarefas que cria uma lista de tarefas e atribui tarefas a subprocessos ou máquinas.

Você está realmente usando todas as linhas?

Se não estiver, e o que você realmente deseja é obter o próximo lote de itens que devem ser despachados aos trabalhadores, existem maneiras mais simples envolvendo LIMIT ou cursores, para buscar apenas um número muito menor de linhas, mas com mais frequência.

Se você deseja pesquisar o status do trabalhador e está procurando apenas alterações entre duas execuções desta consulta, o postgres também possui um mecanismo de notificação, ou você também pode filtrar com mais precisão, etc.