Antipatterns do PostgreSQL: acerte o dicionário pesado JOIN

Continuamos uma série de artigos dedicados ao estudo de formas pouco conhecidas para melhorar o desempenho de consultas PostgreSQL "aparentemente simples":

• registro raro chegará ao meio de JOIN
• matrizes sisyphus
• JOIN e OR prejudiciais
• CTE JOIN CTE

Não pense que eu não gosto muito de JOIN ... :)

Mas muitas vezes sem ele, o pedido é significativamente mais produtivo do que com ele. Portanto, hoje tentaremos nos livrar completamente do JOIN, que consome muitos recursos - com a ajuda de um dicionário.

A partir do PostgreSQL 12, algumas das situações descritas abaixo podem ser interpretadas de maneira um pouco diferente devido à não materialização do CTE por padrão . Esse comportamento pode ser revertido usando a chave MATERIALIZED .

Muitos "fatos" em um vocabulário limitado

Vamos usar um aplicativo muito real - você precisa listar as mensagens recebidas ou tarefas ativas com os remetentes:

 25.01 |  .. |    . 22.01 |  .. |    :   JOIN. 20.01 |  .. |   . 18.01 |  .. |    : JOIN    . 16.01 |  .. |   . 

No mundo abstrato, os autores de tarefas devem ser distribuídos igualmente entre todos os funcionários de nossa organização, mas, na realidade, as tarefas vêm, em regra, de um número bastante limitado de pessoas - “dos superiores” na hierarquia ou “dos aliados” dos departamentos vizinhos (analistas, designers marketing ...).

Vamos supor que em nossa organização de 1000 pessoas, apenas 20 autores (geralmente menos ainda) definam tarefas para cada artista específico e usem esse conhecimento para agilizar a solicitação "tradicional".


Mostramos as últimas 100 tarefas para um artista específico:

 SELECT task.* , person.name FROM task LEFT JOIN person ON person.id = task.author_id WHERE owner_id = 777 ORDER BY task_date DESC LIMIT 100; 

[veja em explicar.tensor.ru]

Acontece que 1/3 do tempo todo e 3/4 das leituras das páginas de dados foram feitas apenas para pesquisar o autor 100 vezes - para cada tarefa exibida. Mas sabemos que dentre essas cem existem apenas 20 diferentes - é possível usar esse conhecimento?

dicionário hstore

Usamos o tipo hstore para gerar um "dicionário" de valor-chave:

 CREATE EXTENSION hstore 

Basta colocar o ID do autor e o nome dele no dicionário, para que possamos extrair mais tarde usando esta chave:

 --    WITH T AS ( SELECT * FROM task WHERE owner_id = 777 ORDER BY task_date DESC LIMIT 100 ) --      , dict AS ( SELECT hstore( -- hstore(keys::text[], values::text[]) array_agg(id)::text[] , array_agg(name)::text[] ) FROM person WHERE id = ANY(ARRAY( SELECT DISTINCT author_id FROM T )) ) --     SELECT * , (TABLE dict) -> author_id::text -- hstore -> key FROM T; 

[veja em explicar.tensor.ru]

Demorou 2 vezes menos tempo para obter informações sobre pessoas e 7 vezes menos dados lidos ! Além de "engano", esses resultados também nos ajudaram a obter a extração em massa de registros da tabela em uma única passagem usando = ANY(ARRAY(...)) .

Entradas da tabela: serialização e desserialização

Mas e se precisarmos salvar no dicionário não um campo de texto, mas um registro inteiro? Nesse caso, a capacidade do PostgreSQL de escrever uma tabela como um valor único nos ajudará:

 ... , dict AS ( SELECT hstore( array_agg(id)::text[] , array_agg(p)::text[] --  #1 ) FROM person p WHERE ... ) SELECT * , (((TABLE dict) -> author_id::text)::person).* --  #2 FROM T; 

Vejamos o que aconteceu aqui:

1. Pegamos p como um alias para o registro completo da tabela de pessoas e montamos uma matriz a partir delas.
2. Essa matriz de entradas foi reformulada em uma matriz de cadeias de texto (person [] :: text []) para colocá-la no dicionário hstore como uma matriz de valores.
3. Ao receber o registro vinculado, o retiramos do dicionário por chave como uma sequência de texto.
4. Precisamos transformar o texto no valor do tipo da tabela de pessoas (para cada tabela, o tipo com o mesmo nome é criado automaticamente).
5. “Implantou” um registro digitado em colunas usando (...).* .

dicionário json

Mas esse truque, como aplicamos acima, não funcionará se não houver um tipo de tabela correspondente para fazer uma "desatenção". Exatamente a mesma situação surgirá, e se, como fonte de dados para serialização, tentarmos usar a linha CTE, e não a tabela "real" .

Nesse caso, as funções para trabalhar com json nos ajudarão:

 ... , p AS ( --   CTE SELECT * FROM person WHERE ... ) , dict AS ( SELECT json_object( --    json array_agg(id)::text[] , array_agg(row_to_json(p))::text[] --   json    ) FROM p ) SELECT * FROM T , LATERAL( SELECT * FROM json_to_record( ((TABLE dict) ->> author_id::text)::json --     json ) AS j(name text, birth_date date) --     ) j; 

Deve-se notar que, ao descrever a estrutura de destino, não podemos listar todos os campos da cadeia de origem, mas apenas aqueles que realmente precisamos. Se tivermos uma tabela "nativa", é melhor usar a função json_populate_record .

Ainda temos acesso ao dicionário uma vez, mas os custos da serialização de json- [de] são bastante altos , portanto, é razoável usar esse método apenas em alguns casos quando o CTE Scan "honesto" se mostra pior.

Teste de desempenho

Portanto, temos duas maneiras de serializar dados em um dicionário - hstore / json_object . Além disso, as matrizes de chaves e valores também podem ser geradas de duas maneiras, com conversão interna ou externa em texto: array_agg (i :: text) / array_agg (i) :: text [] .

Vamos verificar a eficácia de diferentes tipos de serialização usando um exemplo puramente sintético - serializamos um número diferente de chaves :

 WITH dict AS ( SELECT hstore( array_agg(i::text) , array_agg(i::text) ) FROM generate_series(1, ...) i ) TABLE dict; 

No PostgreSQL 11, até um tamanho de dicionário de 2 ^ 12 chaves, a serialização no json leva menos tempo . A combinação de json_object e a conversão de tipo "interno" de array_agg(i::text) é a mais eficiente.

Agora vamos tentar ler o valor de cada chave 8 vezes - porque se você não acessa o dicionário, por que é necessário?




E ... já com cerca de 2 ^ 6 teclas, a leitura do dicionário json começa a perder para a leitura do hstore várias vezes , para jsonb a mesma coisa acontece em 2 ^ 9.

Conclusões finais:

• se você precisar criar um JOIN com registros repetidos repetidamente - é melhor usar a "correspondência de tabela"
• se seu dicionário for pequeno e você ler um pouco dele - você pode usar json [b]
• em todos os outros casos, hstore + array_agg (i :: text) será mais eficiente