Estou tentando otimizar uma visualização do PostgreSQL que agrega dados de uma tabela e depois os une a várias outras tabelas com base em condições específicas. No entanto, o plano de execução não está funcionando tão eficientemente quanto eu esperava.
Criei um exemplo muito simplificado para mostrar esse problema específico.
Tabelas de configuração:
CREATE TABLE public.test_text (
text_id int,
part text,
ordr int
);
CREATE INDEX test_text__text_id_erdr ON test_text(text_id, ordr);
CREATE TABLE public.test_section_selection (
text_id int4,
selection bool
);
CREATE INDEX test_section_selection__selection_text_id ON test_section_selection(selection, text_id);
Nota importante: não há relação de chave estrangeira entre tabelas, pois esses dados são originalmente preenchidos pelo processo ET de outro banco de dados
Gerar dados sintéticos
WITH RECURSIVE parts_generator AS (
SELECT
1 as text_id,
0 as ordr,
md5(random()::text) as part
UNION ALL
SELECT
CASE WHEN text_id % 10 < ordr THEN text_id + 1 ELSE text_id END as text_id,
CASE WHEN text_id % 10 >= ordr THEN ordr + 1 ELSE 0 END as ordr,
md5(random()::text) as part
FROM parts_generator
)
INSERT INTO public.test_text (text_id, ordr, part)
SELECT text_id, ordr, part FROM parts_generator
LIMIT 50000000;
VACUUM ANALYZE test_text;
INSERT INTO public.test_section_selection (text_id, selection)
SELECT DISTINCT text_id, FALSE FROM test_text;
UPDATE test_section_selection
SET selection = TRUE
WHERE text_id % 7777 = 0;
-- Updated Rows: 1168
VACUUM ANALYZE test_section_selection;
7777 é usado porque é um ajuste muito bom para representar dados de reel. Ele também garante que os dados sejam suficientemente espalhados pela tabela (exigindo io aleatório para ler) e fornece uma boa mistura de seções longas e curtas.
Parte de consulta
A consulta em si se parece com isto: a junção de seleção de seção está lá apenas para hs
EXPLAIN ANALYZE
WITH sections AS (
SELECT
tt.text_id,
STRING_AGG(tt.part, '' ORDER BY tt.ordr) AS section_text
FROM
test_text tt
GROUP BY
tt.text_id
)
SELECT *
FROM sections
JOIN test_section_selection selection USING (text_id)
WHERE selection.selection = TRUE;
Isso resulta no seguinte plano de execução:
Merge Join (cost=1.00..2636248.40 rows=55 width=37) (actual time=12.841..14405.822 rows=1168 loops=1)
Merge Cond: (tt.text_id = selection.text_id)
-> GroupAggregate (cost=0.56..2634847.53 rows=547881 width=36) (actual time=0.023..13924.681 rows=9083537 loops=1)
Group Key: tt.text_id
-> Index Scan using test_text__text_id_erdr on test_text tt (cost=0.56..2377998.86 rows=50000032 width=41) (actual time=0.016..7867.727 rows=49959451 loops=1)
-> Index Only Scan using test_section_selection__selection_text_id on test_section_selection selection (cost=0.43..28.34 rows=909 width=5) (actual time=0.018..2.307 rows=1168 loops=1)
Index Cond: (selection = true)
Heap Fetches: 0
Planning Time: 1.679 ms
Execution Time: 14406.020 ms
Como podemos ver no plano de execução, o postgres estima corretamente o número de linhas que esperamos com selection = TRUE: (cost=0.43..28.34 rows=909 width=5) (actual time=0.018..2.307 rows=1168 loops=1). Mas em vez de aplicar o filtro text_id ao filtro test_text e então fazer uma agregação, ele carrega a tabela inteira, agrega todas as 50 mil linhas e somente então aplica o filtro.
Esta pergunta é bem parecida com esta , mas acho que o Postgres deve ser capaz de fazer isso de forma eficiente, pois se eu executar a consulta com um parâmetro em vez de JOIN, ela será executada corretamente:
EXPLAIN ANALYZE
WITH sections AS (
SELECT
tt.text_id,
STRING_AGG(tt.part, '' ORDER BY tt.ordr) AS section_text
FROM
test_text tt
GROUP BY
tt.text_id
)
SELECT * FROM sections WHERE text_id IN (7777,15554,23331,...)
No plano de execução, podemos ver que o postgres aplica o filtro à tabela bruta e está usando um índice para isso:
GroupAggregate (cost=0.56..173269.54 rows=93801 width=36) (actual time=0.040..4.708 rows=989 loops=1)
Group Key: tt.text_id
-> Index Scan using test_text__text_id_erdr on test_text tt (cost=0.56..171574.90 rows=104424 width=41) (actual time=0.027..3.892 rows=6433 loops=1)
Index Cond: (text_id = ANY ('{7777,15554,23331,...}'::integer[]))
Planning Time: 0.313 ms
Execution Time: 4.767 ms
Sim, as estatísticas estão erradas. É assim no caso original também. Mas para testar, podemos aumentar as estatísticas com
ALTER TABLE test_text ALTER COLUMN text_id SET STATISTICS 10000;e isso resolve o problema de estimativa, mas não afeta o problema central com a ordem de execuçãoGroupAggregate (cost=0.56..17846.46 rows=7503 width=36) (actual time=0.036..4.654 rows=989 loops=1) Group Key: tt.text_id -> Index Scan using test_text__text_id_erdr on test_text tt (cost=0.56..17715.13 rows=7507 width=41) (actual time=0.023..3.852 rows=6433 loops=1) Index Cond: (text_id = ANY ('{7777,15554,23331,...}'::integer[])) Planning Time: 0.517 ms Execution Time: 4.712 ms
Então, o nível de desempenho que espero que seja possível é algo como esta consulta:
EXPLAIN ANALYZE
WITH sections AS (
SELECT
tt.text_id,
STRING_AGG(tt.part, '' ORDER BY tt.ordr) AS section_text
FROM
test_text tt
JOIN test_section_selection USING (text_id)
WHERE selection = TRUE
GROUP BY
tt.text_id
)
SELECT *
FROM sections
JOIN test_section_selection selection USING (text_id)
WHERE selection.selection = TRUE;
Ou qualquer forma desta consulta. O plano de execução é
Merge Join (cost=4.67..16478.31 rows=1 width=37) (actual time=0.133..7.554 rows=989 loops=1)
Merge Cond: (tt.text_id = selection.text_id)
-> GroupAggregate (cost=4.23..16437.99 rows=4999 width=36) (actual time=0.124..7.281 rows=989 loops=1)
Group Key: tt.text_id
-> Incremental Sort (cost=4.23..16350.51 rows=4999 width=41) (actual time=0.118..6.428 rows=6433 loops=1)
Sort Key: tt.text_id, tt.ordr
Presorted Key: tt.text_id
Full-sort Groups: 198 Sort Method: quicksort Average Memory: 27kB Peak Memory: 27kB
-> Nested Loop (cost=1.00..16125.56 rows=4999 width=41) (actual time=0.048..5.475 rows=6433 loops=1)
-> Index Only Scan using test_section_selection__selection_text_id on test_section_selection (cost=0.43..25.89 rows=769 width=4) (actual time=0.028..0.124 rows=989 loops=1)
Index Cond: (selection = true)
Heap Fetches: 0
-> Index Scan using test_text__text_id_erdr on test_text tt (cost=0.56..20.86 rows=8 width=41) (actual time=0.004..0.005 rows=7 loops=989)
Index Cond: (text_id = test_section_selection.text_id)
-> Index Only Scan using test_section_selection__selection_text_id on test_section_selection selection (cost=0.43..25.89 rows=769 width=5) (actual time=0.007..0.088 rows=989 loops=1)
Index Cond: (selection = true)
Heap Fetches: 0
Planning Time: 0.294 ms
Execution Time: 7.638 ms
A execução em menos de 10 ms é ~1500 vezes mais rápida do que a consulta original feita na ordem errada. Então eu agradeceria muito sua ajuda com isso.
Infelizmente, como meu caso original é uma view, não posso alterá-lo facilmente para fazer uma junção dentro de uma subconsulta, pois não conheço o filtro de antemão. Até onde vejo, tenho a opção de executar uma versão modificada de uma consulta do lado do cliente como um CTE ou criar uma função Postgres para aplicar os filtros desde o início, mas não gosto de nenhuma das opções e preferiria fazê-la funcionar no cenário geral do caso.
Testado em postgres 16 e 17
O caso original envolve quatro tabelas com uma boa dúzia de colunas e dois níveis de agregação. Não acredito que eu possa juntar tudo e então agregar. Alterar o esquema também não é uma opção, pois é de uma dependência externa.
Atualizar:
Depois de escrever esta pergunta, comecei a questionar minhas suposições
O caso original envolve quatro tabelas com uma boa dúzia de colunas e dois níveis de agregação. Não acredito que eu possa juntar tudo e então agregar.
Eu nunca tinha testado isso antes, mas fiquei surpreso que ficou muito mais rápido depois que fiz isso. Se você estiver enfrentando um problema semelhante com agregação lenta, não tenha medo de juntar tudo antes da agregação.
Aqui está minha definição da visualização resultante, apenas como exemplo. E não é tão complicado quanto imaginei que seria.
aggreagation_layer_one AS (
SELECT
master_data_key,
table_1.search_column_1,
table_1.join_column_1,
table_2.join_column_3,
table_2.search_column_2,
table_3.join_column_4,
table_4."sequence",
STRING_AGG(table_3.text1, '' ORDER BY table_3.ordr) AS section_text,
GREATEST(
ANY_VALUE(table_2.other_column_1),
MAX(table_3.other_column_1)
) AS other_column_1,
GREATEST(
ANY_VALUE(table_1.ingest_timestamp),
ANY_VALUE(table_2.ingest_timestamp),
ANY_VALUE(table_4.ingest_timestamp),
MAX(table_3.ingest_timestamp)
) AS ingest_timestamp,
ANY_VALUE(table_1.other_column_2) AS other_column_2,
ANY_VALUE(table_2.other_column_3) AS other_column_3,
ANY_VALUE(table_2.other_column_4) AS other_column_4,
ANY_VALUE(table_2.other_column_5) AS other_column_5,
ANY_VALUE(table_2.other_column_6) AS other_column_6,
ANY_VALUE(table_2.other_column_7) AS other_column_7
FROM
table_1
JOIN table_2 USING (master_data_key, join_column_1, join_column_2)
LEFT JOIN table_3 USING (master_data_key, join_column_3)
LEFT JOIN table_4 USING (master_data_key, join_column_4)
GROUP BY
master_data_key,
join_column_3,
join_column_4,
search_column_1,
table_1.join_column_1,
table_2.search_column_2,
"sequence"
),
aggreagation_layer_two AS (
SELECT
master_data_key AS master_data_key,
MAX(ingest_timestamp) AS revision_timestamp,
join_column_1, AS id_1,
join_column_3 AS id_2,
ANY_VALUE(other_column_2) AS other_column_2,
search_column_1 AS search_column_1,
STRING_AGG(section_text, '\n' ORDER BY "sequence") AS resulting_text,
ANY_VALUE(other_column_3) AS other_column_3,
ANY_VALUE(other_column_4) AS other_column_4,
ANY_VALUE(other_column_5) AS other_column_5,
search_column_2 AS search_column_2,
MAX(other_column_1) AS other_column_1,
ANY_VALUE(other_column_6) AS other_column_6,
ANY_VALUE(other_column_7) AS other_column_7
FROM aggreagation_layer_one
GROUP BY
master_data_key,
join_column_3,
search_column_1,
join_column_1,
search_column_2
)
SELECT * FROM aggreagation_layer_two
Observe que, se você quiser poder filtrar em uma coluna, essa coluna deve estar na GROUP BYinstrução e não no ANY_VALUE(). Caso contrário, o Postgres não poderá aplicar o filtro aos dados de origem e terá que recorrer à agregação de muito mais dados do que o necessário.